|
Page Protection interfering with Hiding Rows
have a sheet that hides groups of rows based on a cell's information.
I'd prefer this to happen automatically through VBA, but currently I'm stuck using a Toggle Button to hide/unhide these rows. The problem I run into though is that that toggle button works brilliantly until I turn on the page protection. Once I turn on the protection I keep getting errors that the page is protected and can't be updated. And due to the fact that the people who will be using this know absolutely no Excel at all and break formulas on the old sheet regularly... well, the sheet has to be write-protected. The code I'm currently using is this: Sub DENYPASS() Application.ScreenUpdating = False Application.EnableEvents = False If Range("B2") = 1 Then Rows("11:41").EntireRow.Hidden = True Rows("42:72").EntireRow.Hidden = False Else Rows("11:41").EntireRow.Hidden = False Rows("42:72").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub with a private sub to connect it to the Toggle Button Private Sub passdeny_Click() DENYPASS End Sub |
Page Protection interfering with Hiding Rows
Well, shoot. I was excited that there was an answer that was that
simple. I had tried the password unprotect/protect earlier but had the code in the SUB rather than the PRIVATE SUB, and so I was excited to see something so simple. Alas, it doesn't work for some reason. Crap. Thanks anyway, James. Zone wrote: Private Sub passdeny_Click() ActiveSheet.Unprotect password:="yourpassword" DENYPASS ActiveSheet.Protect password:="yourpassword" End Sub James wrote: have a sheet that hides groups of rows based on a cell's information. I'd prefer this to happen automatically through VBA, but currently I'm stuck using a Toggle Button to hide/unhide these rows. The problem I run into though is that that toggle button works brilliantly until I turn on the page protection. Once I turn on the protection I keep getting errors that the page is protected and can't be updated. And due to the fact that the people who will be using this know absolutely no Excel at all and break formulas on the old sheet regularly... well, the sheet has to be write-protected. The code I'm currently using is this: Sub DENYPASS() Application.ScreenUpdating = False Application.EnableEvents = False If Range("B2") = 1 Then Rows("11:41").EntireRow.Hidden = True Rows("42:72").EntireRow.Hidden = False Else Rows("11:41").EntireRow.Hidden = False Rows("42:72").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub with a private sub to connect it to the Toggle Button Private Sub passdeny_Click() DENYPASS End Sub |
Page Protection interfering with Hiding Rows
Have you tried unprotecting the cells they can change and then protecting the
page? That way they can edit only what you want them to be able to edit " wrote: have a sheet that hides groups of rows based on a cell's information. I'd prefer this to happen automatically through VBA, but currently I'm stuck using a Toggle Button to hide/unhide these rows. The problem I run into though is that that toggle button works brilliantly until I turn on the page protection. Once I turn on the protection I keep getting errors that the page is protected and can't be updated. And due to the fact that the people who will be using this know absolutely no Excel at all and break formulas on the old sheet regularly... well, the sheet has to be write-protected. The code I'm currently using is this: Sub DENYPASS() Application.ScreenUpdating = False Application.EnableEvents = False If Range("B2") = 1 Then Rows("11:41").EntireRow.Hidden = True Rows("42:72").EntireRow.Hidden = False Else Rows("11:41").EntireRow.Hidden = False Rows("42:72").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub with a private sub to connect it to the Toggle Button Private Sub passdeny_Click() DENYPASS End Sub |
Page Protection interfering with Hiding Rows
Yep. The problem with that approach it seems, is that I'm trying to put
two pages of information on one page. If they need page "A" they press the toggle button and it hides page "B"s rows, and if they need page "B" - vice versa. The problem comes in with the hiding and unhiding of rows. Since some rows have protected cells, some don't, and some have both, the Macro just tells me that it can't run because of the protected cells. Basically it comes down to: I was trying to minimize the number of sheets in the workbook (not necessarily the size of the file) to appeal to the lowest common denominator of mentality, but I may have to put the third sheet back in. jweasl wrote: Have you tried unprotecting the cells they can change and then protecting the page? That way they can edit only what you want them to be able to edit " wrote: have a sheet that hides groups of rows based on a cell's information. I'd prefer this to happen automatically through VBA, but currently I'm stuck using a Toggle Button to hide/unhide these rows. The problem I run into though is that that toggle button works brilliantly until I turn on the page protection. Once I turn on the protection I keep getting errors that the page is protected and can't be updated. And due to the fact that the people who will be using this know absolutely no Excel at all and break formulas on the old sheet regularly... well, the sheet has to be write-protected. The code I'm currently using is this: Sub DENYPASS() Application.ScreenUpdating = False Application.EnableEvents = False If Range("B2") = 1 Then Rows("11:41").EntireRow.Hidden = True Rows("42:72").EntireRow.Hidden = False Else Rows("11:41").EntireRow.Hidden = False Rows("42:72").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub with a private sub to connect it to the Toggle Button Private Sub passdeny_Click() DENYPASS End Sub |
Page Protection interfering with Hiding Rows
Well, you could do this. Rather simple-minded, it just unprotects both
sheets for the duration of the sub and then reprotects them. Private Sub passdeny_Click() Worksheets("Sheet1").Unprotect password:="yourpassword1" Worksheets("Sheet2").Unprotect password:="yourpassword2" DENYPASS Worksheets("Sheet1").Protect password:="yourpassword1" Worksheets("Sheet2").Protect password:="yourpassword2" End Sub James wrote: Yep. The problem with that approach it seems, is that I'm trying to put two pages of information on one page. If they need page "A" they press the toggle button and it hides page "B"s rows, and if they need page "B" - vice versa. The problem comes in with the hiding and unhiding of rows. Since some rows have protected cells, some don't, and some have both, the Macro just tells me that it can't run because of the protected cells. Basically it comes down to: I was trying to minimize the number of sheets in the workbook (not necessarily the size of the file) to appeal to the lowest common denominator of mentality, but I may have to put the third sheet back in. jweasl wrote: Have you tried unprotecting the cells they can change and then protecting the page? That way they can edit only what you want them to be able to edit " wrote: have a sheet that hides groups of rows based on a cell's information. I'd prefer this to happen automatically through VBA, but currently I'm stuck using a Toggle Button to hide/unhide these rows. The problem I run into though is that that toggle button works brilliantly until I turn on the page protection. Once I turn on the protection I keep getting errors that the page is protected and can't be updated. And due to the fact that the people who will be using this know absolutely no Excel at all and break formulas on the old sheet regularly... well, the sheet has to be write-protected. The code I'm currently using is this: Sub DENYPASS() Application.ScreenUpdating = False Application.EnableEvents = False If Range("B2") = 1 Then Rows("11:41").EntireRow.Hidden = True Rows("42:72").EntireRow.Hidden = False Else Rows("11:41").EntireRow.Hidden = False Rows("42:72").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.EnableEvents = True End Sub with a private sub to connect it to the Toggle Button Private Sub passdeny_Click() DENYPASS End Sub |
| All times are GMT +1. The time now is 08:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com