Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Force user to enable macros, or close file if they don't?

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Ohio
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a workbook_beforesave macro that *must* be enabled. If it's not, I don't want users to open my spreadsheet.

    Is there a way to a) force users to enable macros, b) change the enable macro dialog box to tell them they have to, or c) force the spreadsheet to close itself if they don't enable macros?

  2. #2
    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

    Here's how I do it...
    Hide all of your sheets except one.
    You have to leave one visible, I think...
    Anyway, place a message on this sheet telling your users that macros must be enabled to use your wb.
    If they enable, then unhide all of your sheets.

    Let's assume that the sheet with the "You must
    Enable Macros Message" is called "Message"
    Try this:



    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim s
    For Each s In Worksheets
    If s.Name <> "Message" Then _
    s.Visible = xlSheetVeryHidden
    Next
    End Sub

    Private Sub Workbook_Open()
    Dim s
    For Each s In Worksheets
    s.Visible = xlSheetVisible
    Next
    End Sub




    Tom

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-30 10:33, taffy-cats wrote:
    I have a workbook_beforesave macro that *must* be enabled. If it's not, I don't want users to open my spreadsheet.

    Is there a way to a) force users to enable macros, b) change the enable macro dialog box to tell them they have to, or c) force the spreadsheet to close itself if they don't enable macros?
    Hi Taffy!
    I was raised in Strongsville, Ohio.

    This is one of those I think I understand, but I can only sudgest where to look for the answer.

    If you can set you stuff up in an XLA file with an AUTOEXEC the user will never see the Enable Macros prompt. That will mean doing it as an Add-In. Poke around the startup options for Excel. I really hope this helps.

    Rocky...

  4. #4
    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

    Raised in Cincy...
    Rocky's answer would be a more eloquent and complete solution. My solution is easily defeated, but if the data is not real sensitive, it suffices for the everyday user.
    Tom

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Ohio
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the great suggestions - will try them both as soon as I'm out of meetings today!

    Don't know if the add-ins will work since I'm at a large site & have little control over what gets published to everyone, but it's definitely worth a try.

    And what, is everyone from Ohio now?!?
    :~)

  6. #6
    Board Regular
    Join Date
    Aug 2009
    Location
    washington, dc
    Posts
    379
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Force user to enable macros, or close file if they don't?

    This is very helpful. Is there any way to do this in Powerpoint as well?!

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
  •