Display Visible sheets Only within Input Box

[mholmes]

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

 

[Scoops]

Hi

Test the sheet's Visible property, something like:

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

Regards

Steve

 

[mholmes]

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.

 

[Tom Ogilvy]

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

 

[Mik]

That's done it....
Thanks Tom, Thanks Steve

Appreciate your help SO MUCH !!

 

[Mik]

Thanks for the help earlier, but require a little more info.

My code now reads as below and works well,
but one further amendment is needed.

Assume "Sheet1" is hidden, the Sub displays a input box that lists only
Visible shts and the Cell content of cell "M9" in each of the visible
sheets - something like this:-

The list box displays the following:
Sheet1 - displays nothing as it is hidden
Sheet2 - displays - "2" and cell content of M9
Sheet3 - displays - "3" and cell content of M9
etc...

but I want the visible sheets to be listed starting from Number 1 (not
2),
So,
Sheet1 - displays nothing as it is hidden
Sheet2 - displays - "1" and cell content of M9
Sheet3 - displays - "2" and cell content of M9

Any advice???


Sub FindTagNo()

Dim mysht As Single

myshts = ActiveWorkbook.Sheets.Count

For i = 1 To myshts
If Sheets(i).Visible = xlSheetVisible Then
mylist = mylist & i & " .... " &
ActiveWorkbook.Sheets(i).Range("M9") & vbCr
End If
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 6, 7, or 8 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

 

[Scoops]

Hi Mik

Add a counter for your visible sheets, e.g.:

Dim mysht As Single
Dim VisShts as 'whatever is likely to be your smallest number type -
Byte?

myshts = ActiveWorkbook.Sheets.Count
VisShts = 1 'set the counter

For i = 1 To myshts
If Sheets(i).Visible = xlSheetVisible Then
VisShts = VisShts + 1 'increment the counter
mylist = mylist & VisShts & " .... " &
ActiveWorkbook.Sheets(i).Range("M9") & vbCr
End If
Next i
....

Regards

Steve

 

[Tom Ogilvy]

To the OP.

If you don't put any text in the body of your post, you will exclude some
possible answers.

What do you mean by Inputbox. Do you mean just construct a string to be
displayed in the inputbox prompt:

Dim s as String, sh as worksheet
s = "Visible sheets: " & vbNewline
for each sh in Worksheets
if sh.Visible = xlSheetvisible then
s = s & sh.Name & vbNewline
end if
Next
Inputbox s

 

[Mik]

Thanks for your help here guys.
I have resolved my problem with much help & assistance from various
sources.

Many options were available, and I ended up creating a UserForm which
listed only visible sheets.

The Bulk of the sub I have used is as follows:-

Private Sub UserForm_Initialize()
Dim SheetData() As String
Dim ShtCnt As Integer
Dim ShtNum As Integer
Dim Sht As Object
Dim ListPos As Integer

Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 2)
ShtNum = 1
For Each Sht In ActiveWorkbook.Sheets
If Sht.Visible = True Then
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = Sht.Name
SheetData(ShtNum, 2) = Sht.Range("a1")
ShtNum = ShtNum + 1
End If
Next Sht

With ListBox1
.ColumnWidths = "60 pt; 100 pt"
.List = SheetData
End With
End Sub

Private Sub CancelButton_Click()
OriginalSheet.Activate
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Call OKButton_Click
End Sub

Private Sub OKButton_Click()
Dim UserSheet As Object
On Error Resume Next
Set UserSheet = Sheets(ListBox1.Value)
If UserSheet.Visible Then
UserSheet.Activate
Else
OriginalSheet.Activate
End If
Unload Me
End Sub

Thanks Again.......