Variable Not Defined

djl0525

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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,351
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,237
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,351
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,237

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,351
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,237

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,351
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,237
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,351
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,898
Members
431,772
Latest member
dannyboi1

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
Top