Failed macro because sheet protected

[willisbr]

I have a protected sheet that I have a toggle button in. The toggle hides and unhides columns. The code failes and the toggle wont work if the sheet is protected. Is there a way to build the protect/unprotect within the toggle also? I've tried a few things but so far no good.
The only reason I have the sheet protected is to prevent a function from being overwritten or deleted...so if anything, I thought I can get the toggle to unprotect the sheet...do what it needs to do...and then reprotect again.

I'm using Excel 97

here is the current toggle code:

Private Sub CommandButton1_Click()

a = InputBox("Enter password:", "Security")
If a <> "password" Then
MsgBox "The password you entered is incorrect."
End
End If
With CommandButton1
If CommandButton1.Caption = "Hide Raw Data" Then

Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AE,AG:AH").Select
Selection.EntireColumn.Hidden = True

CommandButton1.Caption = "Show Raw Data"
Else
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AE,AG:AH").Select
Selection.EntireColumn.Hidden = False
Range("C5").Select
CommandButton1.Caption = "Hide Raw Data"
End If
End With
End Sub

 

[HalfAce]

Hello willisbr,
Certainly. You only need to add a couple of lines.

Private Sub CommandButton1_Click()

a = InputBox("Enter password:", "Security")
If a <> "password" Then
  MsgBox "The password you entered is incorrect."
  End
End If

ActiveSheet.Unprotect

With CommandButton1
  If CommandButton1.Caption = "Hide Raw Data" Then
    Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AE,AG:AH").Select
    Selection.EntireColumn.Hidden = True
    CommandButton1.Caption = "Show Raw Data"
  Else
    Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AE,AG:AH").Select
    Selection.EntireColumn.Hidden = False
    Range("C5").Select
    CommandButton1.Caption = "Hide Raw Data"
  End If
End With

ActiveSheet.Protect

End Sub

Note: If you're using a password on your sheet protection then you'll want to change
those two new lines to:
ActiveSheet.Unprotect "Your Password"
and
ActiveSheet.Protect "Your Password"

That's all you should need to do.

 

[pfarmer]

I have a protected sheet that I have a toggle button in. The toggle hides and unhides columns. The code failes and the toggle wont work if the sheet is protected. Is there a way to build the protect/unprotect within the toggle also? I've tried a few things but so far no good.
The only reason I have the sheet protected is to prevent a function from being overwritten or deleted...so if anything, I thought I can get the toggle to unprotect the sheet...do what it needs to do...and then reprotect again.

I'm using Excel 97

here is the current toggle code:

I tend to avoid the use of vba to change a sheet's protection, especially without defining the workbook and sheet by name. The reason in my case is that I run many different workbooks, some at the same time and avoid having the code in one having the possibility of trying to operate on the wrong 'Active Sheet'. In any case have you tried just protecting the sheet and allowing 'Format'. I don't run 97 any more so not sure of the options for sheet protection.

In other versions when you protect a sheet you get a set of checkboxs for selecting what operations you allow or disallow on a sheet. I believe this is often a better solution then protecting or unprotecting a sheet., unless there are other reasons to do so.

Perry

 

[willisbr]

It protects it ok but when I get a run time error 1004 "unprotect method of worksheet class failed" when I click it again with it protected.

Any ideas? version issue?

Private Sub CommandButton1_Click()

a = InputBox("Enter password:", "Security")
If a <> "password" Then
MsgBox "The password you entered is incorrect."
End
End If

ActiveSheet.Unprotect

With CommandButton1
If CommandButton1.Caption = "Hide Raw Data" Then
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AE,AG:AH").Select
Selection.EntireColumn.Hidden = True
CommandButton1.Caption = "Show Raw Data"
Else
Range("C:D,F:G,I:J,M:P,R:S,U:V,X:Y,AA:AE,AG:AH").Select
Selection.EntireColumn.Hidden = False
Range("C5").Select
CommandButton1.Caption = "Hide Raw Data"
End If
End With

ActiveSheet.Protect

End Sub

 

[HalfAce]

Well, it's been a while since I've used xl '97, I wrote that in xl '03, and am testing it here
with xl 2K. (and it's working fine.)
ActiveSheet.Unprotect (if memory serves) used to work fine in '97 as well so I wouldn't think
it's a version issue.

I assume when you get the error and hit Debug that it's the line ActiveSheet.Unprotect that's highlighted in yellow. (yes?)
I can't think of any reason off hand why you'd be getting that error.

 

[pfarmer]

Well, it's been a while since I've used xl '97, I wrote that in xl '03, and am testing it here
with xl 2K. (and it's working fine.)
ActiveSheet.Unprotect (if memory serves) used to work fine in '97 as well so I wouldn't think
it's a version issue.

I assume when you get the error and hit Debug that it's the line ActiveSheet.Unprotect that's highlighted in yellow. (yes?)
I can't think of any reason off hand why you'd be getting that error.

I believe you would get this error in a macro if you try to change the worksheet format if the sheet is protected. This is why I mentioned checking the box marked 'Format', there is 'Format Rows', 'Format Columns', and I think 'Format Cells'. Not sure if this applies to 97 however.

Perry

 

[HalfAce]

Unless I'm wrong, the error seems to be occurring while trying to unprotect the sheet as
opposed to trying to change any formatting while the sheet's protected, otherwise it
would likely be the "Unable to set the Hidden property of the range class" error.

 

[pfarmer]

Unless I'm wrong, the error seems to be occurring while trying to unprotect the sheet as
opposed to trying to change any formatting while the sheet's protected, otherwise it
would likely be the "Unable to set the Hidden property of the range class" error.

I was paying attention to 1004, so I think you are probably correct. One thing to try with 97 is to first select the sheet to make it truely active within the macro and then run the unprotect routine. I am not sure of this in 97 but if run from a command button the button may be taking the focus away from the sheet leaving no active sheet. Without having 97 can the control be set to not take focus on click?

Perry

 

[Ivan F Moala]

For Excel97 you need to set the commandbuttons [Takefocusonclick] to False

 

[willisbr]

Perfect thats what it was!! Allright

Lastly, can the password type with a security feature? IE: ****** instead of the actual text showing as I type it in?