VBA to show Blank Sheet when Excel Contains are not Enabled

SamarthSalunkhe

New Member
Joined
Jun 14, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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)

1631805906056.png


Error (Snapshot is below)

1631805934449.png




VBA Code:
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,442
Office Version
  1. 2013
Platform
  1. Windows
Try qualifying your worksheet ...
VBA Code:
'Step 6:  Hide the Start Sheet
    ThisWorkbook.Worksheets("Macro-disabled").Visible = xlSheetVeryHidden
 

SamarthSalunkhe

New Member
Joined
Jun 14, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Try qualifying your worksheet ...
VBA Code:
'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

VBA Code:
Private Sub Workbook_Open()

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

End Sub

Code 2

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,442
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 
Solution

SamarthSalunkhe

New Member
Joined
Jun 14, 2021
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,442
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know (y)
 

Forum statistics

Threads
1,148,022
Messages
5,744,368
Members
423,865
Latest member
SimSum

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top