How to program a toggle control button

[Arup C]

Hi everybody,
I am trying to create toggle button to alternately protect and unprotect my
excel sheet. How can I solve this problem since I know very little about vba.

 

[Dave Peterson]

How about an alternative?

Add a button to your favorite toolbar.
(in xl2003 menus)
Tools|Customize|Commands tab|Tools category

Scroll down the righthand side and look for "Protect Sheet"

Drag that icon to your favorite toolbar.

In xl2007, you can customize the quick access toolbar the same kind of way:
http://www.rondebruin.nl/notinribbon.htm
(from Ron de Bruin's site)

 

[Arup C]

Hi Dave,
Thanks a lot for your help but I have to use this file on other computers
also in your suggested way I am able to use this shortcut only on one
computer as the custom toolbar setting is lost when I use the format on other
computers. To enable this I have to copy the xlb file along with this file.
Do you have any solution for this problem? I will be looking forward for your
help.

 

[Dave Peterson]

You could create a macro that would show the sheet protection dialog.

Then you (as the developer) don't have to worry about passwords:

Option Explicit
Sub ShowProtDialog()
Application.Dialogs(xlDialogProtectDocument).Show
End Sub

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

In xl2007, those toolbars and menu modifications will show up under the addins.