Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom toolbar & macros

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Custom toolbar & macros

    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

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom toolbar & macros

    Thanks, Andrew, I'll gice that a try

    Stuart

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Custom toolbar & macros

    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.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Custom toolbar & macros

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom toolbar & macros

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •