Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Button Moves when sheet printed | Excel Discussion (Misc queries) | |||
command button isn't working | New Users to Excel | |||
How do I create a command button to jump from sheet to sheet in a. | Excel Worksheet Functions | |||
Non Working Cells after command button | Excel Programming | |||
generating sheet with command button & code | Excel Programming |