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

 

[Micron]

Then it must be that the value is being interpreted as 0 - not hard to check:

Sheets("Colorado").Select
Debug.Print [B2]
debug.print Sheets("Colorado").Range("B2")

Same for any other problem sheet. Selecting sheets to check cell values is inefficient and unnecessary. You can also eliminate all the IFs and explicitly reference a sheet cell (range) and not rely on Excel to look at the correct sheet:

Sheets("Job data").Visible = Sheets("Job data").Range("A3") = 0

If I reversed the logic, change = to <>
Please post code within code tags (vba button on posting toolbar) to maintain indentation and readability.
EDIT - you turn off alerts but don't turn them back on?

 

[Skyybot]

You might want to think about using Select Case for this bit of code.?

 

[Peter_SSs]

As requested already: When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It has been mentioned above about shorter, more efficient, ways to write such code. This is one way.
BTW, there is no need to turn off alerts for hiding/unhiding worksheets.

Sub HideSheets_v2()
  Dim ShName As Variant
  
  Application.ScreenUpdating = False
  Sheets("Job Data").Visible = Sheets("Job Data").Range("A3").Value <> 0
  For Each ShName In Array("Arizona", "Utah", "Colorado", "Idaho")
    Sheets(ShName).Visible = Sheets(ShName).Range("B2").Value <> 0
  Next ShName
  Application.ScreenUpdating = True
End Sub

 

[Micron]

I might do it this way

Sub HideSheets()
'condensed version 2
Dim rng As Range

Application.DisplayAlerts = False
Set rng = Range("B2")
Sheets("Job data").Visible = Sheets("Job data").Range("A3") = 0
Sheets("Arizona").Visible  = Sheets("Arizona").rng = 0
Sheets("Utah").Visible = Sheets("Utah").rng = 0
Sheets("Colorado").Visible = Sheets("Colorado").rng = 0
Sheets("Idaho").Visible = Sheets("Idaho").rng = 0
Application.DisplayAlerts = True

End Sub

 

[Peter_SSs]

I might do it this way

Wondering if you tested that code? Errors for me.

 

[Micron]

I tested parts of it but when actually put together I see a problem with rng and grouping.
The idea comes from a shortcut method of hiding controls that works in Access, but it seems that in Excel, one needs to enclose that which needs to be evaluated first in parentheses. So what works for me is
Sheets("1").Visible = (Sheets("1").Range("B2") = 0)
or
Sheets("1").Visible = (rng = 0)

EDIT - forgot to point out that my code is in a standard module, in case that helps.

 

[RoryA]

The issue is that you can't use Sheets("Idaho").rng = 0. rng is a variable (that refers to a range on a specific sheet) not a property of a worksheet.

 

[Micron]

The issue was that at first, my rng object included a reference to the sheet so by subsequently prefacing rng with a sheet reference I was duplicating the sheet reference?
Range is definitely a property of a worksheet

Worksheet.Range property (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Mattandy55]

I was able to get it to work finally.. some different formatting causing the original code to not read the cells correctly. But was able to clean up and streamline the coding based on what you all have helped with. Thank you all for your inputs and assistance. Still diving into VBA more and more so it was great to see all the different and more efficient ways to write the code.
Thank you all again, much appreciated!