MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro Implementation


Posted by Riki Phelan on October 08, 2001 8:07 AM

I have the following macro given to me to toggle between Protect/Unprotect, but I'm having trouble attaching it to an Option Button and get it to run.

Private Sub ProtectionToggle()
If ProtectionToggle.Value = True Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub

Any ideas why it will not run? What's the "Private" bit mean?

Tks, Riki


Posted by Juan Pablo on October 08, 2001 9:27 AM

Replace the macro with

Private Sub ProtectionToggle()
ActiveCell.Activate
If ProtectionToggle.Value = True Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub

Juan Pablo

Posted by Riki Phelan on October 08, 2001 10:47 AM

Tks Juan - still a bit confused tho. I'm having trouble attaching your macro to my Option Button from the FORMS menu.

What's the Private bit signify? All my other (recorded) macros kick in with Sub etc. These I can attach to the Option Button without any problems, but with yours I'm having trouble.

Cheers, Riki

Posted by Juan Pablo on October 08, 2001 11:11 AM

Ok, i think you need to elaborate a little bit more on what you're doing / trying to do.

For what i understood, you have (should, i guess) two option buttons, one that is "Protect sheet" and the other one "Unprotect Sheet", right ? what you want is when you click on one of these that you protect / unprotect your sheet ?

Juan Pablo

Posted by Riki on October 08, 2001 2:36 PM

Juan,

I was thinking along the lines of only the one option button that toggles either ON or OFF. Is that at all possible?

Tks, Riki

Posted by Josef Masopust on October 08, 2001 5:01 PM

Try this :-

Sub ProtectionToggle()
If Application.ExecuteExcel4Macro("get.document(7)") = True Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub

Posted by Riki on October 09, 2001 2:52 AM

Thankyou!

Thanks Josef - exactly what I was after, and thanks Juan. I'm learning a lot from this forum and I appreciate those who respond.

Cheers Riki Try this :- Sub ProtectionToggle()