Variable Not Defined

[djl0525]

The code is stopping on the "sh" in the first line,
For Each sh In ThisWorkbook.Sheets


Private Sub Workbook_Open()
Application.ScreenUpdating = False

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "sheet1" Then sh.Visible = True Else sh.Visible = xlVeryHidden
Next sh

Sheet1.Visible = xlVeryHidden
Sheet2.Visible = xlVeryHidden

Worksheets("Welcome").Activate
Range("A1").Select
Application.ScreenUpdating = True

End Sub


What am I missing, please?

DJ

 

[Norie]

Just declare sh.

Private Sub Workbook_Open() 
Dim sh As Worksheet
Application.ScreenUpdating = False 

For Each sh In ThisWorkbook.Sheets 
If sh.Name <> "sheet1" Then 
       sh.Visible = True 
Else
       sh.Visible = xlVeryHidden 
End If
Next sh 

Sheet1.Visible = xlVeryHidden 
Sheet2.Visible = xlVeryHidden 

Worksheets("Welcome").Activate 
Range("A1").Select 
Application.ScreenUpdating = True 

End Sub

 

[djl0525]

That worked. I tried to apply it to another code, but I was not successful. See my Dim line. I added it because this code stops on the for Each wsSheet... line. I also tried using

Dim sh As Worksheet

and changing wsSheet to sh. But that didn't work either. Can somebody help me again, please?



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

'Hide everything except Sheet1

Application.ScreenUpdating = False

For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.CodeName = "Sheet1" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next sh
Application.ScreenUpdating = True

End Sub

 

[Norie]

Does this work?

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

'Hide everything except Sheet1 

Application.ScreenUpdating = False 

For Each wsSheet In ThisWorkbook.Worksheets 
If wsSheet.CodeName = "Sheet1" Then 
wsSheet.Visible = xlSheetVisible 
Else 
wsSheet.Visible = xlSheetVeryHidden 
End If 
Next wsSheet
Application.ScreenUpdating = True 

End Sub

 

[djl0525]

It stops on

wsSheet.Visible = xlSheetVeryHidden

When I mouseover the line it says xlSheetVisible = -1
As a result, it's not making Sheet1 VeryHidden like it used to to when I close the workbook.

DJ

 

[Norie]

Why do you have xlSheetVeryHidden?

You only have xlVeryHidden in the other code.

 

[djl0525]

Good questions. I don't know...

I copied both of these codes from another workbook where they work fine. I thought I could copy them and they'd be okay.

I changed that line to
wsSheet.Visible = xlVeryHidden
and it still stopped there.

Any suggestions on what will fix it?

DJ

 

[Norie]

When you say it 'stopped' what exactly do you mean?

Did you get any error messages?

 

[djl0525]

I have determined that the problem is not with this code. I ran a test so I could better explain what is happening and it worked fine. I opened and closed the file again and it didn't work fine. I have other code that I think it somehow interfering with this code. I need to do a little investigating.

Thanks for your help thus far.

DJ

 

[Norie]

DJ

Is this connected to your other posts?

If it is I would suggest in one of the threads you outline exactly what you are trying to achieve.