Display userform before unhiding worksheets

Question

I have code that displays a worksheet that tells the user to turn on macros, with other sheets being kept hidden. Once the workbook is re-opened, the remaining worksheets are displayed and the sheet that tells the user to turn on macros is hidden.

I want to display a userform before displaying the hidden worksheets.

Option Explicit
Const origsave As String = "C:\SDK Engineering\iMudCalc Software"
Const origwrkbksave As String = "C:\SDK Engineering\iMudCalc Software\iMudCalc.xlsm"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet

'don't display workbook
Application.Visible = False

'Unhide the Starting Sheet
Sheets("Warning").Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Warning" Then
        ws.Visible = xlVeryHidden
    End If
Next ws

If Len(Dir(origsave)) > 0 Then
    MkDir (origsave)
Else
    ActiveWorkbook.SaveCopyAs (origwrkbksave)
End If
End Sub

Private Sub WorkBook_Open()
Dim ws As Worksheet

Application.Visible = False

    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws

    Sheets("Warning").Visible = xlVeryHidden

    'this is where I need the workbook to stay invisible and display a userform if macros are enabled...
    frmOnLoad.Show

End Sub

I am using Excel 2013.

If the macros are disabled from the start, how do you expect your macro to run the first time? Correct me if I'm wrong, but if the Excel macro security settings are set to not allow macros by default, I'm pretty sure that no matter what they do in the first instance of Excel (turning macros on/off), when they completely exit Excel, and then re-open it, it'll have macros disabled. — BruceWayne
@BruceWayne I think what he's suggesting is you have a macro enabled workbook and you save it with all sheets hidden except for one sheet that says "You must enable macros to use this workbook" or something. If they have macros enabled though, when it runs, it unhides all the sheets and hides the "You must enable macros to use this workbook" sheet. As described, it can work that way. — user1274820

user1274820 user1274820 · Accepted Answer · 2017-04-05T21:35:07

I believe you're looking for something like this.

Modify as needed.

Place in ThisWorkbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Warning").Visible = xlSheetVisible
Dim ws
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Warning" Then ws.Visible = xlVeryHidden
Next ws
ThisWorkbook.Save
End Sub
Private Sub Workbook_Open()
Dim ws
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Warning" Then ws.Visible = xlSheetVisible
Next ws
Sheets("Warning").Visible = xlVeryHidden
End Sub

Example:

Display userform before unhiding worksheets

1 Answers