Force Macro Enable/Disable

[Maurush]

Hello guys,

I have a problem concerning macro's ... I made an excel workbook that will
show a sheet if macro's are disabled. Only that sheet will appear, in which it says
that people need to enable the macro's, close the file and then reopen.

When macro's are enabled people will not see that sheet again, and can start
answering questions and go through the rest of my sheets.

I found some files doing that under the name 'Splash' I don't know if anybody is familiar
with that term.

Anyway the problem is the following, I have multiple sheets that I want to show,
but I always want to start with the sheet called "Start Enquete" ... but the problem I
have is that other pages will show, after I have saved the file or quit the program.

The codes that I have are the following:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False

        Call HideSheets

        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With

    ThisWorkbook.Save
    ThisWorkbook.Saved = True

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False

        Call HideSheets

        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    End With

    ThisWorkbook.Save
    ThisWorkbook.Saved = True

End Sub

Private Sub Workbook_Open()
    
ActiveSheet.Unprotect ("")
    
    'Unhide all worksheets
    
            With Application
                .EnableCancelKey = xlDisabled
                .ScreenUpdating = False
        
                Call UnhideSheets
        
                .ScreenUpdating = True
                .EnableCancelKey = xlInterrupt
            End With
         
ActiveSheet.Protect ("")
        
End Sub

Private Sub HideSheets()

Dim Sheet As Object

    With Sheets("Macros")
        .Visible = xlSheetVisible
            For Each Sheet In Sheets
                If Not Sheet.Name = "Macros" Then
                    Sheet.Visible = xlSheetVeryHidden
                End If
            Next
        Set Sheet = Nothing
    End With

End Sub

Private Sub UnhideSheets()

    Sheets("Start Enquéte").Visible = xlSheetVisible
    Sheets("Macros").Visible = xlSheetVeryHidden
    Sheets("Clustermanagers blad 1").Visible = xlSheetVisible
    Sheets("Clustermanagers blad 2").Visible = xlSheetVisible
    Sheets("Clustermanagers blad 3").Visible = xlSheetVisible
    Sheets("Clustermanagers blad 4").Visible = xlSheetVisible
    Sheets("End").Visible = xlSheetVisible
       
End Sub

What do I need to change at the code so that always when open the file,
when macro's are enabled, the file starts with Sheets("Start Enquéte")???

Thanks for the help!!

 

[Maurush]

If macros are disabled your workbook_open won't do anything. So no, wrong.

I finally found the solution, and I didn't need to put the part .select at the end of the unhidesheet.

I have used the following codes for the hide/unhiding of the sheets:

Private Sub HideSheets()

Dim Sheet As Object

    With sheets("EnableMacro")
        .Visible = xlSheetVisible
            For Each Sheet In sheets
                If Not Sheet.Name = "EnableMacro" Then
                    Sheet.Visible = xlSheetVeryHidden
                End If
            Next
        Set Sheet = Nothing
    End With

End Sub

Private Sub UnhideSheets()

    sheets("Start Enquéte").Visible = xlSheetVisible
    sheets("EnableMacro").Visible = xlSheetVeryHidden

End Sub

With the subs BeforeClose and BeforeSave I have used the following (both the same code):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

        With Application
        .EnableCancelKey = xlDisabled
        .ScreenUpdating = False

        Call HideSheets

        .ScreenUpdating = True
        .EnableCancelKey = xlInterrupt
    
    End With

    ThisWorkbook.Save
    ThisWorkbook.Saved = True

End Sub

But I also put this code in the Commandbutton:

Private Sub CommandButton11_Click()

        If ComboBox1.Value > 1 And ComboBox2.Value = "Nee" Then
                      
             If MsgBox("Weet u zeker dat u het document wilt opslaan en afsluiten?", vbYesNo + vbQuestion, "Bestand opslaan en afsluiten") = vbYes Then
                                                                              
                        With Application
                        .EnableCancelKey = xlDisabled
                        .ScreenUpdating = False
                
                        Call HideSheets
                
                        .ScreenUpdating = True
                        .EnableCancelKey = xlInterrupt
                    
                    End With
                
                    ThisWorkbook.Save
                    ThisWorkbook.Saved = True
                    ActiveWorkbook.Close

             End If

I found out that when I closed the file everything was working correctly, but when I saved it with
the commandbutton, after reopening the workbook the worksheet that appeared was the last one
before closing. This when I did not put your part in Glenn, sheet("..").select. --> this helped me open the first sheet if macros were enabled.
But if the macros were disabled, I couldn't get the right worksheet appear.

The last thing that is important is the sub workbook_open() here you need to call unhide sheets:

            With Application
                .EnableCancelKey = xlDisabled
                .ScreenUpdating = False
        
                Call UnhideSheets
        
                .ScreenUpdating = True
                .EnableCancelKey = xlInterrupt
            End With

With those codes it is possible to always show a screen when macros are disabled with
information about switching macros on. After close as well as save.

I hope it will be handy for someone someday! Anyway thanks Glenn for your help.

Maurush.

 

[GlennUK]

I'm glad that you got it working.