Protection Using VBA 1

[mdav2]

I am trying to simply turn the protection on and off using VBA. The code I use is ACTIVESHEET.PROTECT and ACTIVESHEET.UNPROTECT. However, this command keeps returning errors when I run it from a toggle button.

Runtime error '1004'
Unprotect method of worksheet class failed

All I am trying to do is show/hide a few rows but I need to keep protection switched on. I have not used a password so I can't see why it doesn't work. The strange thing is once it fails you can't turn the protection on or off without closing the workbook down and re-opening.


The code I used was:

Private Sub tgbStatus_Click()

ActiveSheet.Unprotect

If tgbStatus = True Then
Rows("4:6&quot.Select
Selection.EntireRow.Hidden = True
tgbStatus.Caption = "Show Status"
Range("A9&quot.Select
Else
Rows("4:6&quot.Select
Selection.EntireRow.Hidden = False
tgbStatus.Caption = "Hide Status"
Range("A9&quot.Select
End If

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

 

[Loomah]

Hi mdav
Dunno what to say - this works perfectly for me - even after I tried to break it!

Is there anything else on the sheet (apart from the button and data) that might affect the protection? Yes, I am clutching at straws!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

If tgb_Status is a command button from the controls toolbox (and being as it's a private sub, I guess it is), you will need to set the "Take Focus On Click" property (of the button) to false, otherwise, the commandbutton will have the focus and there is no "activesheet"
HTH
~Geoff~

 

[Loomah]

I've assumed that the control is a toggle button!
And....
the selection of range A1 would take care of the focus problem, wouldn't it?

Still can't see a problem with it!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

Loomah - the original code didn't select A1
Havn't got time to test right now but yes,I believe selecting A1 should work - this "creates" an activesheet, as does not taking the focus on click - however, you only need to set a property once - if you have a select statement, it'll be run every time the code is.... HTH
~Geoff~

 

[Loomah]

It looks like I'm picking holes but I'm not. I just can't see any problem with the code as posted.

But a very valid point has been made. Is the sheet active before the code is run?? Is the code getting as far as selecting A9 (pointless question but...)?

Select A9 (or whatever) then click the button.

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

The code falls over at the activesheet.unprotect line
Therefore, you would need another select statement BEFORE the activesheet.unprotect line, which is unnecessary (???) 'cos all you have to do is take the focus off the cb
HTH
~Geoff~

 

[mdav2]

Thanks, it was that the button still had the focus and I got around it using the code below:

Code
----
Private Sub tgbStatus_Click()

Sheets("Estimated Outturn&quot.Select
[a9].Select
ActiveSheet.Unprotect

If tgbStatus = True Then
Rows("4:6&quot.Select
Selection.EntireRow.Hidden = True
tgbStatus.Caption = "Show Status"
Range("A9&quot.Select
Else
Rows("4:6&quot.Select
Selection.EntireRow.Hidden = False
tgbStatus.Caption = "Hide Status"
Range("A9&quot.Select
End If

ActiveSheet.Protect

End Sub