VeryHidden causing a problem...?

[Darin Kramer]

HOwdie,

I have a workbook with many tabs - some hidden others not.

My simple macro does not work! It is meant to select a tab when a user
clicks on a simple text box on the navigation screen (just a series of
boxes)

When the tabs are not visible I have "veryhidden" them.
I think that is causing the error..? is that possible, and if so what do
I need to change to fix it...?

(effectively when a user runs the macro asking to be taken to a sheet
which is not visible he needs to get a message saying - change xyz
before proceeding. Currently the code is not working...and Im
stumped...

Regards

D


Sub Nav_app_F()


Dim ws As Worksheet
Dim blnMessageNeeded As Boolean

blnMessageNeeded = True

For Each ws In ActiveWorkbook.Worksheets
If ws.Visible Then
If ws.Name = "APP_F" Then
Sheets("APP_F").Select
Range("A1").Select
blnMessageNeeded = False
End If
End If
Next
If blnMessageNeeded Then MsgBox ("Select Part 2 to view this
sheet.")

End Sub

 

[Norman Jones]

Hi Darin,

Perhaps try:

'=============>>
Public Sub Nav_app_F()
Dim WB As Workbook
Dim SH As Worksheet

Set WB = ThisWorkbook
Set SH = WB.Sheets("APP_F")

With SH
If .Visible = xlSheetVeryHidden Then
MsgBox Prompt:="Select Part 2 to view this sheet."
Else
Application.Goto reference:=.Range("A1")
End If
End With
End Sub
'<<=============

 

[Guest]

Darin,

Explicitly test for xlSheetVisible. xlSheetVeryHidden has a numeric value
of 2 which still takes you inside the If. Try:

If ws.Visible = xlSheetVisible Then
If ws.Name = "APP_F" Then
Sheets("APP_F").Select
Range("A1").Select
blnMessageNeeded = False
End If
End If

 

[Darin Kramer]

Awesome - works perfectly!!!