Command Button not working once sheet is protected
 

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!

Command Button not working once sheet is protected
 

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

109 to go.
--
Regards,
Tom Ogilvy

"Janice" wrote in message
...
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!

Command Button not working once sheet is protected
 

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" wrote in message
...
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="ABCD"
Rows(1).Delete
Activesheet.Protect Password:="ABCD"
End Sub

109 to go.
--
Regards,
Tom Ogilvy

"Janice" wrote in message
...
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!

Command Button not working once sheet is protected
 

True. Hopefully Janice can see the difference.

--
Regards,
Tom Ogilvy

Trevor Shuttleworth wrote in message
...
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" wrote in message
...
Private Sub CommandButton1_Click()
ActiveSheet.Unprotect Password:="ABCD"
Rows(1).Delete
Activesheet.Protect Password:="ABCD"
End Sub

109 to go.
--
Regards,
Tom Ogilvy

"Janice" wrote in message
...
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!

Command Button not working once sheet is protected
 

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!

Command Button not working once sheet is protected
 

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


"Janice Hanna" wrote in message
...

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!