Toolbar button to main worksheet area

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I would like to know if it is possible to put the "Protect sheet" toolbar
button onto the actual worksheet instead of on the toolbar. This is
probably a really dumb question, but I want to have the facility to send a
worksheet with this option without affecting someone else's toolbar. I've
created a button with the following code, but it can be overridden with the
toolbar button.
Private Sub ToggleButton1_Click()
ActiveCell.Select
If ToggleButton1 = True Then
ToggleButton1.Caption = "SHEET UNPROTECTED"
ToggleButton1.ForeColor = &HFF&
ToggleButton1.BackColor = &H8000000F
ActiveSheet.Unprotect
ActiveWindow.Panes(3).Activate
Else
ToggleButton1.Caption = "SHEET PROTECTED"
ToggleButton1.ForeColor = &H808000
ActiveSheet.Protect
ActiveWindow.Panes(3).Activate
End If
End Sub

Thank you
Rob
 
I think you'd be fighting a losing battle if you want your caption to accurately
reflect the protection state of the worksheet.

You could check each time the user selects a different cell, but that means you
could be behind if they don't select a cell between hitting Tools|protection and
clicking your button.

I think I'd use a button from the forms toolbar (or a commandbutton from the
controltoolbox toolbar) and just have the caption say:

Toggle Protection.

I'd use code like:

Option Explicit
Private Sub CommandButton1_Click()
If Me.ProtectContents _
Or Me.ProtectDrawingObjects _
Or Me.ProtectScenarios Then
On Error Resume Next
Me.Unprotect Password:=""
If Err.Number <> 0 Then
MsgBox "Unprotect failed"
Else
MsgBox Me.Name & " is unprotected"
End If
On Error GoTo 0
Else
Me.Protect
MsgBox Me.Name & " is protected"
End If
End Sub

But if the user changes the password, you've got a bigger problem anyway.
 
Thanks Dave,
I used your suggested code and works fine.
Is there a reason why the toolbar buttons function differently if that
command is put in a VB code as it would certainly make life easier if they
behaved the same way?
Rob
 
I thought that the problem was that your toggle button sometimes reflected the
wrong state. If I always used your button/code to protect/unprotect the
worksheet, then I don't think I'd have a problem.

But as soon as I used Tools|Protection to protect/unprotect the sheet, then your
caption would be out of sync with the actual sheet protection.

But I'm not sure I understand your question.

(I took the "ActiveWindow.Panes(3).Activate" line out of your code and your code
worked ok for me. (And as long as I didn't use a password, it got back in sync
as soon as I hit clicked the button--but for one moment, it was out of sync.)
 
Yes Dave, the problem is as you say, "But as soon as I used Tools|Protection
to protect/unprotect the sheet, then your caption would be out of sync with
the actual sheet protection."
It's not a real problem, just a nuisance if that should occur.
But thanks again.
Rob
 
Back
Top