Announcement

Collapse
No announcement yet.

Automatically Close Workbook If Macros Are Disabled

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Automatically Close Workbook If Macros Are Disabled



    If a user disables macros when opening a file, is there a way to have the excel file automatically close?

    Thanks!

    - Steve

  • #2
    Re: Closing Workbook Automatically If Macros Are Disabled

    That would require a macro ...

    But you could keep a user from seeing any data.
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Closing Workbook Automatically If Macros Are Disabled

      Nope. See http://www.ozgrid.com/download/ and download EnableMacros.zip

      However, this is FAR from bullet proof and relies on the user saving upon close. Personally, whether the user wants to enable macros, or not, is entirely up to them.

      Comment


      • #4
        Re: Automatically Close Workbook If Macros Are Disabled

        Thanks to both of you for the quick reply!

        To qualify my question, I agree, Dave, that the user has (and should have) the right to decide whether to open files with macros. I don't want to force users to accept the macro if they're not comfortable with it; I'm more concerned that if they don't accept the macro, the results they see in the file won't be right, and they'll rely on bad info.

        Thanks again.

        - Steve

        Comment


        • #5
          Re: Automatically Close Workbook If Macros Are Disabled

          Yes, I figured that. However, from my experience end users are VERY easily confused by the numerous pit-falls the above code has. In fact, it often causes more problems than it resolves.

          I would simply email each user (and keep a record for butt covering) to each end user telling them that macros MUST be enabled and explain how to set their security level to Medium.

          Comment


          • #6
            Re: Automatically Close Workbook If Macros Are Disabled

            Originally posted by Dave Hawley
            I would simply email each user (and keep a record for butt covering) to each end user telling them that macros MUST be enabled and explain how to set their security level to Medium.
            Hi Dave,
            I have faced the issue in discussion quite often at work and here's what I've done:

            I have created a separate Sheet with a simple message to the users about the macro security level being set to high (and that they should set it to medium by doing bla...bla...bla... and then reopen the file).

            The workbook activates this sheet every time the file is saved and closed (Workbook_BeforeClose event).

            In this way, the next time the file opens, if security is high, the users see the message and take the appropriate action.

            In case security is medium or low, this sheet gets hidden and so the users see whatever they are supposed to see.

            So far, it has saved me from dozens of phone calls and e-mail going back and forth.

            Comment


            • #7
              Re: Automatically Close Workbook If Macros Are Disabled

              Dave,

              This is what I use - obviously you can get around it quite simply, but if you protect the VBA project you make it that little bit harder...

              I have an EnableMacros sheet that will show how the user either enables macros or changes macro security levels.

              This sheet is always displayed unless the user enables macros.

              Hope it helps.
              Attached Files

              Comment


              • #8
                Re: Automatically Close Workbook If Macros Are Disabled

                tstav and Magriza, all the user has to do is Enable Macros, then cloose WITHOUT saving.

                Comment


                • #9
                  Re: Automatically Close Workbook If Macros Are Disabled

                  But the macros could do a Save (with the usual hiding) in the BeforeClose event. And in the Save event they could do the hiding before save and restoring after save transparently to the user.

                  No?
                  Entia non sunt multiplicanda sine necessitate.

                  Comment


                  • #10
                    Re: Automatically Close Workbook If Macros Are Disabled

                    The attached file works on the same principle as Dave's enable macros but it does not require the user to save upon close.

                    I had to modify it slightly because my original file also featured a login screen.
                    Attached Files
                    Reafidy

                    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                    Comment


                    • #11
                      Re: Automatically Close Workbook If Macros Are Disabled

                      But the macros could do a Save (with the usual hiding) in the BeforeClose event.
                      A forced save is not good, IMO. Say they delete a sheet/cells/rows/columns etc and close?

                      Comment


                      • #12
                        Re: Automatically Close Workbook If Macros Are Disabled

                        Dave,

                        See my post #10 for an alternative it does not require a forced save. And doesnt require the user to save before close.
                        Reafidy

                        Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                        Comment


                        • #13
                          Re: Automatically Close Workbook If Macros Are Disabled

                          That's a cool way. Nice thinking outside the box. I stand corrected.

                          Comment


                          • #14
                            Re: Automatically Close Workbook If Macros Are Disabled

                            Don't make me look at the file Reafidy, I'm sleepy. What did you do?
                            Entia non sunt multiplicanda sine necessitate.

                            Comment


                            • #15


                              Re: Automatically Close Workbook If Macros Are Disabled

                              Originally posted by shg
                              Don't make me look at the file Reafidy, I'm sleepy. What did you do?
                              LOL! SHG the oZ Zombie.

                              Kiwi Black Magic:

                              Code:
                              Private Sub Workbook_BeforeClose(Cancel As Boolean)
                                  bIsClosing = True
                              End Sub
                              Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
                                  Dim wsArray() As Variant
                                  Dim iCnt As Integer
                                  Application.ScreenUpdating = 0
                                  
                                  Splash.Visible = True
                                  
                                  For Each wsSht In ThisWorkbook.Worksheets
                                      If Not wsSht.CodeName = "Splash" Then
                                          If wsSht.Visible = True Then
                                              iCnt = iCnt + 1: ReDim Preserve wsArray(1 To iCnt)
                                              wsArray(iCnt) = wsSht.Name
                                          End If
                                          wsSht.Visible = xlSheetVeryHidden
                                      End If
                                  Next
                                  
                                  Application.EnableEvents = 0
                                  ThisWorkbook.Save
                                  Application.EnableEvents = 1
                                  
                                  If Not bIsClosing Then
                                      For iCnt = 1 To UBound(wsArray)
                                          Worksheets(wsArray(iCnt)).Visible = True
                                      Next iCnt
                                      Splash.Visible = False
                                      Cancel = True
                                  End If
                                  
                                  Application.ScreenUpdating = 1
                              End Sub
                              Private Sub Workbook_Open()
                                  Dim wsSht As Worksheet
                              
                                  For Each wsSht In ThisWorkbook.Worksheets
                                       wsSht.Visible = xlSheetVisible
                                  Next wsSht
                                  
                                  Splash.Visible = xlSheetVeryHidden
                                
                                  bIsClosing = False
                              End Sub
                              Reafidy

                              Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                              Comment

                              Working...
                              X