Force Enable Macros for user (Small issue)

[IAIPICI]

Hey I have found some code on VBA Express which I really like. It is the smoothest running code to perform is task that I have come across, however I have a small problem that is annyoning me.

For orignal post: http://www.vbaexpress.com/kb/getarticle.php?kb_id=578

If I close the the workbook and changes have been made it will, ofcourse promt me to save work. However, when this happens it automaticlly switches to the sheet I have created to be displayed when macros are disabled. I only what this to happen at the worksheet open stage if the macros are disabled. Having the screen change before people close it may confuse them.

Below is code submitted by johnske on VBA express

Option Explicit 
 
Private Sub Workbook_Open() 
     
    With Application 
         'disable the ESC key
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 
         
        Call UnhideSheets 
         
        .ScreenUpdating = True 
         're-enable ESC key
        .EnableCancelKey = xlInterrupt 
    End With 
     
End Sub 
 '
Private Sub UnhideSheets() 
     '
    Dim Sheet As Object 
     '
    For Each Sheet In Sheets 
        If Not Sheet.Name = "Prompt" Then 
            Sheet.Visible = xlSheetVisible 
        End If 
    Next 
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden 
     '
    Application.Goto Worksheets(1).[A1], True '< Optional
     '
    Set Sheet = Nothing 
    ActiveWorkbook.Saved = True 
     
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    With Application 
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 
         
        Call HideSheets 
         
        .ScreenUpdating = True 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 
 
Private Sub HideSheets() 
     '
    Dim Sheet As Object '< Includes worksheets and chartsheets
     '
    With Sheets("Prompt") 
         '
         'the hiding of the sheets constitutes a change that generates
         'an automatic "Save?" prompt, so IF the book has already
         'been saved prior to this point, the next line and the lines
         'relating to .[A100] below bypass the "Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] = "Saved" 
         '
        .Visible = xlSheetVisible 
         '
        For Each Sheet In Sheets 
            If Not Sheet.Name = "Prompt" Then 
                Sheet.Visible = xlSheetVeryHidden 
            End If 
        Next 
         '
        If .[A100] = "Saved" Then 
            .[A100].ClearContents 
            ThisWorkbook.Save 
        End If 
         '
        Set Sheet = Nothing 
    End With 
     '
End Sub

Does anyone know if there is a way around this... thanks.

 

[Domenic]

Thankyou once again Domenic for your assistance.

You're very welcome! And, as you can see, shg has given you an efficient alternative.