Variable Not Defined

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,235
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Just declare sh.

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

Well-known Member
Joined
Dec 11, 2004
Messages
1,235
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Does this work?
Code:
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

Well-known Member
Joined
Dec 11, 2004
Messages
1,235

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
Why do you have xlSheetVeryHidden?

You only have xlVeryHidden in the other code.
 

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,235

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
When you say it 'stopped' what exactly do you mean?

Did you get any error messages?
 

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,235
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,379
Members
412,589
Latest member
ArtBOM
Top