Make all sheets visible except one

[ndendrinos]

Trying to edit code to make all sheets visible EXCEPT sheet "Log"

Here is the code:

Private Sub Workbook_Open()
Application.ScreenUpdating = False

      Dim Sheet As Worksheet
      'make all sheets visible
      For Each Sheet In Worksheets
            If Sheet.Name <> "Prompt" Then
                  Sheet.Visible = xlSheetVisible
                  
            End If
      Next Sheet
      'hide the prompt and go to A1 on sheet1
      Sheets("Prompt").Visible = xlSheetVeryHidden
      Application.Goto Sheet1.[A1], scroll:=True
      'clean up
      Set Sheet = Nothing
      ActiveWorkbook.Saved = True
      Application.ScreenUpdating = True

End Sub

 

[Domski]

Hi,

This should do it:

Private Sub Workbook_Open()

Dim Sheet As Worksheet

Application.ScreenUpdating = False
    
'make all sheets visible

For Each Sheet In Worksheets
    
    If Sheet.Name <> "Log" Then

        Sheet.Visible = xlSheetVisible
                  
    End If

Next Sheet
      
'hide the prompt and go to A1 on sheet1

Sheets("Log").Visible = xlSheetVeryHidden
      
'clean up
      
Set Sheet = Nothing

ActiveWorkbook.Saved = True

Application.ScreenUpdating = True

End Sub

 

[ndendrinos]

Hello Domski
Tried your edit and it sure hides sheet "Log" BUT now the sheet "Prompt" stays visible.
Fixed it with:

Next Sheet
     
'hide the prompt and go to A1 on sheet1

Sheets("Prompt").Visible = xlSheetVeryHidden
     
'clean up
     
Set Sheet = Nothing

ActiveWorkbook.Saved = True

Application.ScreenUpdating = True

End Sub

 

[Domski]

Trying to edit code to make all sheets visible EXCEPT sheet "Log"

You really mean you want all sheets visible EXCEPT sheet "Log" and "Prompt" then?

Dom

 

[ndendrinos]

Correct, sorry I should have been more precise.
Thank you very much for your help.
BTW you've answered a post about keeping tab on who edits a file.
I have posted my answer too and maybe you will like to have a look
Best regards and thanks again

 

[Domski]

Hi,

No worries.

This might be better:

Private Sub Workbook_Open() 

Application.ScreenUpdating = False

Dim Sheet As Worksheet
      
'make all sheets visible

For Each Sheet In Worksheets
    
    If Sheet.Name <> "Log" Or Sheet.Name <> "Prompt" Then

        Sheet.Visible = xlSheetVisible
                  
    End If

Next Sheet
      
'hide the log and prompt sheets

Sheets("Log").Visible = xlSheetVeryHidden
Sheets("Prompt").Visible = xlSheetVeryHidden
      
'clean up
      
Set Sheet = Nothing

ActiveWorkbook.Saved = True

Application.ScreenUpdating = True

End Sub

Had a quick look at other post. The idea sounds fine although not tested the code. It all depends on the workbook being saved and the person enabling macros when the file is open which can both be easily avoided though. I think the OP is interested in who has already been using a file rather than a future project as well, but i could be wrong.

Dom