Command Button not working once sheet is protected

[Janice]

I have created an Excel template used for data entry. I
have added command button with a macro behind it to hide
and unhide rows. However, once I protect the worksheet so
that the user cannot make changes, the command button
won't work. HELP! I created 110 of these silly things
and now they don't work!

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="ABCD"
Rows(1).Delete
Activesheet.Protect Password:="ABCD"
End Sub

109 to go.

 

[Trevor Shuttleworth]

Tom

Rows(1).Delete ? Maybe a bit radical for hiding a row ?

Janice

one way:

Private Sub CommandButton1_Click()
'Toggle the Hidden Status for Row 1
ActiveSheet.Unprotect Password:="ABCD"
Rows(1).Hidden = Not Rows(1).Hidden
ActiveSheet.Protect Password:="ABCD"
End Sub

Regards

Trevor

 

[Tom Ogilvy]

True. Hopefully Janice can see the difference.

 

[Janice Hanna]

Gentleman, thank you for your input. Deleting a row is definitely too
drastic. As I indicated I have the command buttons doing what I need
them to do, that is until I protect the worksheet so that the user can
not alter its original form. Here is a sample of the code for expanding
the rows:

Private Sub CommandButton4_Click()
Dim StartRow As Long
Dim EndRow As Long
StartRow = 13
EndRow = 14
Rows(StartRow & ":" & EndRow).Hidden = False
End Sub

Then here is the code for the button that hides the rows:

Private Sub CommandButton3_Click()
Dim StartRow As Long
Dim EndRow As Long
StartRow = 13
EndRow = 14
Rows(StartRow & ":" & EndRow).Hidden = True
End Sub

I have tried the following change and VB does not like it. Since I am
new to VB code and just learning from a book, I have no idea what I am
missing. I am researching both through my book and through the
Internet. But I wanted to see if either of you could give me a hint.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect password:="corp_rate"

Dim StartRow As Long
Dim EndRow As Long
StartRow = 8
EndRow = 11
Rows(StartRow & ":" & EndRow).Hidden = True
ActiveSheet.Protect password:"corp_rate"

End Sub

Thanks!
Janice


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Trevor Shuttleworth]

Janice

the 'ActiveSheet.Protect password:"corp_rate"' line needs '=' after the ':'

should be 'ActiveSheet.Protect password:="corp_rate"'

As I suggested, you could halve the number of buttons if you toggled the
hidden on and off ... but your choice.

Regards

Trevor