CODEWITHSUNDEEP
vbaexcel
BobSki
BobSki

Reputation: 1552

VBA macro hiding sheets

I know there are answers everywhere for my question but I've tried them all, I'm not sure what I am missing, or what have you. Basically I have about 20 tabs in an excel spreadsheet. On workbook open, I'm trying to hide them all, but this isn't working out. When my workbook opens, i see sheets flashing for a second then everything is like it normally is. I'm basically looping through all the worksheets and making them hidden like this...

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = xlSheethidden
Next ws

LogForm.Show

End Sub

What is it that I'm doing wrong? I'm able to hide them if i simply do...

sheets("Project").visible=false

But since users might add more worksheets i'm trying to make sure that my code is dynamic.

Upvotes: 0

Views: 5466

Answers (1)

Tyeler
Tyeler

Reputation: 1118

There's a few different ways we can go about having the Userform visible while the workbook isn't.

Minimize your workbook on open. 

Private Sub Workbook_Open() 'This is in the Workbook Code
    Application.WindowState = xlMinimized
    UserForm1.Show xlModeless
End Sub

Hide an individual worksheet.

Sub mySub()
    Dim ws as Worksheet: Set ws = ThisWorkbook.Sheets(1)

    ws.Visible = xlSheetHidden
End Sub

Completely hide the Excel Window (Only Userform will show)

Sub mySub()
    UserForm1.Show xlModeless
    Application.Visible = False 'Can also be placed in Workbook_Open() to hide on open
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Application.Visible = True
End Sub

To hide a single workbook

Sub MySub()
    Windows("MyWorkbook").Visible = False
End Sub

Upvotes: 2

Related Questions