Custom toolbar & macros

Topic

New Reply

Hi.

For one reason or another, I revently created two macros which respectively protect and unprotect all the sheets in the active workbook in one fell swoop.

Now, unlike Word, there doesn’t appear to be a “normal.xlt” type template in which to add the macros, so they went into the current workbook. I then created a new toolbar, and put two buttons on it, and attached the macros one to each.

So far so good, but when I close the workbook with the macros in it, the buttons still remain. In fact, the buttons remain in Excel, period, with or without a workbook open. But clicking on the buttons opens up the workbook with the macro in it, and tries to run it (although it fails to find the macro, even with the named workbook open, but that is another story).

So to the question: Where do I put the macros so that they will be accessible to all workbooks, and can I amend the buttons, or do I need to have to re-create them?

Thanks.

Stuart

PS. I’m using Excel2000

Reply | Quote

Replies

What you need is a Personal Macro book called Personal.xls which should be stored in your xlstart directory. You simply create the file from a blank workbook. You can then add all your macros and functions that you would like to be available to all your workbooks. The file will be opened each time you start Excel. When you go to assign macros you can then make sure they are preceeded by Personal.xls, e.g Personal.xls!Macro1

Before you save it, you can hide it by going to Windows, Hide, so that it will be inconspicuous whilst you are working.

Andrew C

Reply | Quote

Thanks, Andrew, I’ll gice that a try

Stuart

Reply | Quote

Would you be willing to share your macro?

I’m still trying to teach myself Excel macros (used to do quite a few in Lotus) and I know that would be a very handy one as I hate like heck going page by page locking and unlocking. And the odd time you forget to lock even one page, is when a user inadvertently changes some critical formula.

Thanks if you are willing to share, but understand if not.

Reply | Quote

FWIW, here’s mine:

Sub BatchSheetProtect()
Application.ScreenUpdating = False
Dim strPwd As String
strPwd = “password”
For Each Sheet In ActiveWorkbook.Worksheets
Sheet.Protect Password:=strPwd
Next Sheet
Application.ScreenUpdating = True
End Sub

Sub BatchSheetUnProtect()
Application.ScreenUpdating = False
Dim strPwd As String
strPwd = “password”
For Each Sheet In ActiveWorkbook.Worksheets
Sheet.Unprotect Password:=strPwd
Next Sheet
Application.ScreenUpdating = True
End Sub

Substitute “password’ with your actual password.

Reply | Quote

Hi.

John’s macro is fairly identical to mine, except that I didn’t put in the password string (there are times when it’s necessary, but generally I just want to be able to protect the sheet from unintentional changes).

Note the “Application.ScreenUpdating = False” line. You need this line in here to turn off screen refresh, otherwise it will flicker through all your worksheets in turn applying the settings, but don’t forget to turn it back on again afterwards!

Regards,

Stuart

Reply | Quote