Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How to Check Macro Security Setting?

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    India
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I have to distribute an Excel File containing macros to various users.
    How can i make the file/macro check macro security setting of the users machine and advise him to enable macros otherwise the macros in the distributed excel file will not work.
    Can anyone please help.
    Niranjan

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can't. If the user has blocked macros, you can't have a macro run automatically to check settings (the whole point of blocking macros is not having macros doing things automatically). I would therefore advise the following.

    When closing the workbook, have the auto_close macro select a separate sheet before saving, which contains the warning you wish to give new users. By selecting the sheet before saving, you make sure the sheet is active when a user opens the workbook.
    If the user has macros enabled, you can have the "Auto_open" macro select another sheet immediately, so the warning disappears.

    I hope this will help.

    Marc

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Leiden, The Netherlands
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a small improvement. Use the Workbook_BeforeSave event instead of Auto_Close to do the trick. This allows the user to close the workbook without saving. The Workbook_BeforeSave event should be placed in the code of the workbook object, which you can access by right-clicking on the Excel symbol left of the "File" menu option, and selecting "View Code".

    Place the following code:
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
            Cancel As Boolean)
       Worksheets("Warning").Activate
    End Sub
    Where "Warning" should be replaced with the corresponding sheet name in your workbook.

    Marc

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    India
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Marc

    [ This Message was edited by: Niranjan on 2002-04-23 00:23 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    If your code has part in the integrity of your data, formats, ect.
    You may want to hide all of your sheets except the one showing the message.
    If macros is not enabled then your workbook will be more protected.

    Tom

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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