VBA to show Blank Sheet when Excel Contains are not Enabled

[SamarthSalunkhe]

Hello Everyone,

I am using LoginInstance (Snapshot is below), but when Excel Contains are not Enabled then the user can see data of sheet. I am trying to use the below VBA code (where I have given command as the user can see a blank sheet with the name of ("Macro-disabled") when Excel Contains are not Enabled) but I'm getting an Error (Snapshot is below).

Can someone provide a solution?

LoginInstance (Snapshot)

Error (Snapshot is below)

Private Sub Workbook_Open()

'To Show Login Form
    LoginInstance = 0
    
    Application.Visible = False
    
    frmLogin.Show

'Step 1:  Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible

'Step 5:  Loop to next worksheet
    Next ws

'Step 6:  Hide the Start Sheet
    Sheets("Macro-disabled").Visible = xlVeryHidden

End Sub

 

[GWteB]

Try qualifying your worksheet ...

'Step 6:  Hide the Start Sheet
    ThisWorkbook.Worksheets("Macro-disabled").Visible = xlSheetVeryHidden

 

[SamarthSalunkhe]

Try qualifying your worksheet ...

'Step 6:  Hide the Start Sheet
    ThisWorkbook.Worksheets("Macro-disabled").Visible = xlSheetVeryHidden

Hi GWteB,

Thank you for your reply,

I have tried your modification but facing the same error.

Actually, I have added below two codes combine.

If I use both codes separately it works, but if I combine them I am facing an error.

Code 1

Private Sub Workbook_Open()

'To Show Login Form
    LoginInstance = 0
  
    Application.Visible = False
   
    frmLogin.Show

End Sub

Code 2

Private Sub Workbook_Open()
 'Step 1:  Declare your variables
    Dim ws As Worksheet

'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible
Sheet2.Visible = False
'Step 5:  Loop to next worksheet
    Next ws

'Step 6:  Hide the Start Sheet
    Sheets("Macro-disabled").Visible = xlVeryHidden
 
End Sub

 

[GWteB]

If you did not left out any code involved, then I can only explain the run-time error from the fact that your worksheet "Macro-disabled" doesn't exist. So do check for typos.
Obviously, the code behind your Userform may do things that conflict with what follows after the Userform is closed, but I can't see that from distance.

Private Sub Workbook_Open()

    'Step 1:  Declare your variables
    Dim ws As Worksheet
    
    LoginInstance = 0
    Application.Visible = False
    FormLogin.Show

    'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Step 3: Unhide All Worksheets
        ws.Visible = xlSheetVisible
    Next ws

    'Step 6:  Hide the Start Sheet
    ThisWorkbook.Worksheets("Macro-disabled").Visible = xlSheetVeryHidden
    
    Application.Visible = True
End Sub

 

[SamarthSalunkhe]

If you did not left out any code involved, then I can only explain the run-time error from the fact that your worksheet "Macro-disabled" doesn't exist. So do check for typos.
Obviously, the code behind your Userform may do things that conflict with what follows after the Userform is closed, but I can't see that from distance.

Private Sub Workbook_Open()

    'Step 1:  Declare your variables
    Dim ws As Worksheet
   
    LoginInstance = 0
    Application.Visible = False
    FormLogin.Show

    'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Step 3: Unhide All Worksheets
        ws.Visible = xlSheetVisible
    Next ws

    'Step 6:  Hide the Start Sheet
    ThisWorkbook.Worksheets("Macro-disabled").Visible = xlSheetVeryHidden
   
    Application.Visible = True
End Sub

Thank you GWteB,

The code is working now perfectly. ???

 

[GWteB]

You are welcome and thanks for letting me know