Variable Not Defined

djl0525

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Why do you have xlSheetVeryHidden?

You only have xlVeryHidden in the other code.
 
Upvote 0
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
 
Upvote 0
When you say it 'stopped' what exactly do you mean?

Did you get any error messages?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,290
Members
447,853
Latest member
olddutch7

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
Back
Top