Display Visible sheets Only within Input Box

I have the following sub which currently opens a Input Box that
displays the Sheet No. and the content of Cell A1 (for all sheets in
the Active workbook).

I want to adapt this sub, so that only visible sheets are shown in the
Input Box.

Can anybody please help?
I'm a newbie, and found extracts of the below sub by searching these
informative groups.

If you do have advice for me, it would be greatly appreciated if you
could spell out the code.

Thanks in advance.

************************************

Sub GotoSheet()

Dim mysht As Single

myshts = ActiveWorkbook.Sheets.count

For i = 1 To myshts
mylist = mylist & i & " .... " &
ActiveWorkbook.Sheets(i).Range("a1") & vbCr
Next i

On Error Resume Next
mysht = InputBox("To display the Calculation for a particular Tag
No," & _
vbCr & "Type the Number adjacent to the that Tag." & _
vbCr & vbCr & "(Example. Type 1, 2, or 3 etc...)" & vbCr & vbCr
& mylist)

If mysht = False Then Exit Sub

On Error GoTo Error
Sheets(mysht).Select

If mysht = mysht Then Exit Sub

Error:
MsgBox "Invalid Tag reference entered," & vbCr & "Please try again
....."

End Sub

Hi

Test the sheet's Visible property, something like:

If Sheets(i).Visible = False Then ...

Regards

Steve

Hi

Test the sheet's Visible property, something like:

If Sheets(i).Visible = False Then ...

Regards

Steve

Thanks for the Prompt reply.

I understand that by adding the line
If Sheets(i).Visible = False Then ... "perform routine"

However, correct me if I'm wrong, but by adding

If Sheets(i).Visible = False Then 'some code'

tells the routine to check if any sheets are not visible,
if not visible then peform some code, if visible perform some code.....

But, how do i actually tell the Input box to display Only visible
Sheets?

Excuse me if i'm not making sense.

Checks each sheet and only add information for those that pass the test (are
visible):

For i = 1 To myshts
if sheets(i).visible = xlSheetVisible then
mylist = mylist & i & " .... " & _
ActiveWorkbook.Sheets(i).Range("a1") & vbCr
end if
Next i

--
Regards,
Tom Ogilvy

"[email protected]" wrote:

> I have the following sub which currently opens a Input Box that
> displays the Sheet No. and the content of Cell A1 (for all sheets in
> the Active workbook).
>
> I want to adapt this sub, so that only visible sheets are shown in the
> Input Box.
>
> Can anybody please help?
> I'm a newbie, and found extracts of the below sub by searching these
> informative groups.
>
> If you do have advice for me, it would be greatly appreciated if you
> could spell out the code.
>
> Thanks in advance.
>
> ************************************
>
> Sub GotoSheet()
>
> Dim mysht As Single
>
> myshts = ActiveWorkbook.Sheets.count
>
> For i = 1 To myshts
> mylist = mylist & i & " .... " &
> ActiveWorkbook.Sheets(i).Range("a1") & vbCr
> Next i
>
> On Error Resume Next
> mysht = InputBox("To display the Calculation for a particular Tag
> No," & _
> vbCr & "Type the Number adjacent to the that Tag." & _
> vbCr & vbCr & "(Example. Type 1, 2, or 3 etc...)" & vbCr & vbCr
> & mylist)
>
> If mysht = False Then Exit Sub
>
> On Error GoTo Error
> Sheets(mysht).Select
>
> If mysht = mysht Then Exit Sub
>
> Error:
> MsgBox "Invalid Tag reference entered," & vbCr & "Please try again
> ....."
>
> End Sub
>
>