VBA Issue

[Mattandy55]

I have a code that should be basic enough.. But for some reason when running it doesn't work correctly for Idaho and Colorado. It hides Colorado and Idaho even though B2 on each sheet aren't 0. Job Data, Arizona, and Utah all work perfectly. Any assistance is greatly appreciated.

Sub HideSheets()

Application.DisplayAlerts = False
    Sheets("Job Data").Select
    If [A3] = 0 Then
Sheets("Job data").Visible = False
Else
Sheets("Job Data").Visible = True
End If
    Sheets("Arizona").Select
    If [B2] = 0 Then
Sheets("Arizona").Visible = False
Else
Sheets("Arizona").Visible = True
End If
Sheets("Utah").Select
    If [B2] = 0 Then
Sheets("Utah").Visible = False
Else
Sheets("Utah").Visible = True
End If
Sheets("Colorado").Select
    If [B2] = 0 Then
Sheets("Colorado").Visible = False
Else
Sheets("Colorado").Visible = True
End If
Sheets("Idaho").Select
    If [B2] = 0 Then
Sheets("Idaho").Visible = False
Else
Sheets("Idaho").Visible = True
End If



Application.ScreenUpdating = True

End Sub

 

[RoryA]

Range is definitely a property of a worksheet

Yes Range is, but rng is not - it's a variable that you declared, so you can't use Sheets("some sheet").rng. Nor do you need to since rng already refers to a specific existing range on one specific sheet.

 

[Micron]

Well, until I smarten up I will believe that rng is a range object since I declared it as such.
Dim rng As Range
so rng is a property of a sheet. I still think the issue was the double reference to the sheet.

 

[Peter_SSs]

I still think the issue was the double reference to the sheet.

No. The issue is that you have declared rng as a range object and set it to cell B2 on a particular sheet - the active sheet when the code is run.


Easy to demonstrate
Make all sheets visible and make Arizona the active one. Now step through your code. It will process (hide or not hide) 'Job data' since that has nothing to do with rng. It will process (hide or not hide) 'Arizona' since rng is cell B2 on that sheet. It will not process 'Utah' because rng is set to B2 on 'Arizona', not Utah'

Make all sheets visible again and make 'Utah' the active sheet then step through the code. This time it will fail on 'Arizona' because rng will have been set to B2on 'Utah'

 

[RoryA]

Well, until I smarten up I will believe that rng is a range object since I declared it as such.
Dim rng As Range

Yes, that is correct.

so rng is a property of a sheet.

No, rng is a variable that you created. It's also an object not a property.

If you write:

Dim ws as worksheet
Dim rng as Range
set ws = sheets("Sheet1")
set rng = ws.Range("A1")

when you type the ws. in the last line, Intellisense will pop up a list of properties and methods of a worksheet. rng is not one of them, whereas Range is.