Solved: Counting Visible Sheets in a workbook

[itipu]

Guys, I have this code

[VBA] Sub Go2sheet()
myShts = ActiveWorkbook.Sheets.Count
For i = 0 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to generate SC ticket from." & vbCr & vbCr & myList, "Report & Service Centre Ticket")
Sheets(mySht).Select
End Sub[/VBA]

It basically brings up an InputBox

with 1 - Sheet1
2 - Sheet2 and so one, and asks for input...

However in my workbook Sheet1 & Sheet2 are veryhidden and not used, so I dont want to count them... How can I do that?

Also ideall I want only to count & show Sheets that contain in their name "Computer"

Thanks a lot

Mike

[matthewspatrick]

[vba]
Function CountVisibleSheets(Optional NameContains)

Dim sht As Object

For Each sht In ActiveWorkbook.Sheets
If sht.Visible = True Then
If Not IsMissing(NameContains) Then
If InStr(1, sht.Name, NameContains, vbTextCompare) > 0 Then _
CountVisibleSheets = CountVisibleSheets+1
Else
CountVisibleSheets = CountVisibleSheets+1
End If
End If
Next

End Function
[/vba]

[itipu]

I am not to good with VB and can't quite figure out who can I use this function with my InputBox...

Thanks a lot

Mike

[mdmackillop]

Hi Itipu,
I think this would be simpler with a Userform. Note that sheet Computer3 is hidden in this example.

[Bob Phillips]

Here is another approach using dialog sheets

[vba]

Option Explicit

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If Worksheets(i).Visible = xlSheetVisible Then
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
End If
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = xlOn Then
'do your bit with a selected sheet
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub
[/vba]

[mdmackillop]

I still think this would be simpler with a Userform

[itipu]

Thanks a lot it works great used the UserForm...

One more little thing, I have a sub ExtractData on Form1... Sub ExtractData is activated when user clicks a button... within that sub I have

UserForm1.Show which activates UserForm1 as in mdmackillop's example...

Now the issue is when user closes UserForm1 (pressing x)

I have

Sub UserForm1_Terminate()
Unload Me
Exit Sub
End Sub

Which closes UserForm1 ok, but I still get errors since my ExtractData sub on Form1 was not closed, any idea how to close that?

Thanks a lot !!!!!!!!!!!!!!!!!!!!!!!

Much obliged!

Mike

[Bob Phillips]

I think you need to post the workbook with the problem.

[itipu]

Basically if you click Test SC button, it will show UserForm... however if you press X on user form it will barf because UserForm is opened from a sub within a different form and I am not sure how to exit that sub...

Thanks a lot again!

[Bob Phillips]

Declare anothe module variable in frmExtract

[vba]

Public HowClosed As String
[/vba]
and in frmExtract add this procedure code

[vba]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Select Case CloseMode
Case 0: frmExtract.HowClosed = "Forced"
Case 1: frmExtract.HowClosed = "Selected"
End Select
End Sub
[/vba]
and change the extract procedure to

[vba]

Sub ExtractData()
Const EMAIL_SHEET As String = "Test_SC"
Dim rng As Range
Dim dteTest As Date
Dim sh As Worksheet

UserForm1.Show

If HowClosed = "Selected" Then

With ActiveSheet

dteTest = DateSerial(Year(Date), Month(Date) - 1, Day(Date))

.Columns("A:E").Sort Key1:=.Range("B2"), _
Order1:=xlDescending, _
Header:=xlYes
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown)).Resize(, 4)
rng.AutoFilter
.Columns("A:E").AutoFilter Field:=2, Criteria1:="=No Local Logon", Operator:=xlOr, Criteria2:="<" & Format(dteTest, Cells(rng.Rows.Count, 2).NumberFormat)
.Columns("A:E").AutoFilter Field:=4, Criteria1:="=Linux-Desktop", Operator:=xlOr, Criteria2:="=Linux-Server"
Set rng = rng.SpecialCells(xlCellTypeVisible)
ActiveSheet.AutoFilterMode = False
ActiveSheet.UsedRange.AutoFilter
On Error Resume Next
Set sh = Worksheets(EMAIL_SHEET)
On Error GoTo 0
If sh Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
Set sh = ActiveSheet
sh.Name = EMAIL_SHEET
End If
sh.Cells.ClearContents
rng.Copy sh.Range("A1")
sh.Range("A11").EntireColumn.AutoFit
Unload frmExtract

End With

End If

End Sub
[/vba]

[lior03]

hello
i am trying to use mathewpatrick function to find the opposite- namely how many hidden sheets there are in the activeworkbook.
[VBA]
Function CountnonVisibleSheets(Optional NameContains)
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = False Then
If Not IsMissing(NameContains) Then
If InStr(1, sht.name, NameContains, vbTextCompare) > 0 Then _
CountnonVisibleSheets = CountnonVisibleSheets + 1
Else
CountnonVisibleSheets = CountnonVisibleSheets + 1
End If
End If
Next
End Function
[/VBA]
what is wrong ?
thanks

[itipu]

It works very well on closing... however if you do Select a Sheet, and add a breakpoint to If HowClosed = "Selected" Then in extract proceedure, you'll find it will jump to End If without actually generating a repor...

I thought it could have been...

Private Sub UserForm1_QueryClose(Cancel As Integer, CloseMode As Integer)
Select Case CloseMode
Case 0: frmUserForm1.HowClosed = "Forced"
Case 1: frmUserForm1.HowClosed = "Selected"
End Select
End Sub

But that did not change anything...
Thx

Mike

[Bob Phillips]

I did make a mistake in the instructions, the UserForm_QueryClose procedure should have been added to Userfrom1 not frmExtract as I said. I think however that you must have spotted this for the forced close to work. When in the correct form, it works fine for me.

[itipu]

The exiting works and generates no errors, what doesn't work though is if you go ahead and try to generate a SC_TEST sheet... It skips it... You can compare this macro with improvements as you suggested to the one I sent last time.. if you click the button, select computer sheet it will create SC_Test sheet... it does not do that anymore...

Thanks

Mike

[Bob Phillips]

Follow the instructions!

The procedure is called UserForm_QueryClose NOT Userform1_QueryClose

[Bob Phillips]

hello
i am trying to use mathewpatrick function to find the opposite- namely how many hidden sheets there are in the activeworkbook.
[vba]
Function CountnonVisibleSheets(Optional NameContains)
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = False Then
If Not IsMissing(NameContains) Then
If InStr(1, sht.name, NameContains, vbTextCompare) > 0 Then _
CountnonVisibleSheets = CountnonVisibleSheets + 1
Else
CountnonVisibleSheets = CountnonVisibleSheets + 1
End If
End If
Next
End Function
[/vba] what is wrong ?
thanks

[vba]

Function CountnonVisibleSheets(Optional NameContains)
Dim sht As Object
For Each sht In ActiveWorkbook.Sheets
If sht.Visible <> xlSheetVisible Then
If Not IsMissing(NameContains) Then
If InStr(1, sht.Name, NameContains, vbTextCompare) > 0 Then _
CountnonVisibleSheets = CountnonVisibleSheets + 1
Else
CountnonVisibleSheets = CountnonVisibleSheets + 1
End If
End If
Next
End Function
[/vba]

[itipu]

Thanks it does work great!

One last final thing

How can I make a Case statement based on which button is clicked...

so if I have

Private Sub CommandButton4_Click()
ExtractData
End Sub

Private Sub CommandButton5_Click()
ExtractData
End Sub

Private Sub CommandButton6_Click()
ExtractData
End Sub

I want to have a case statement in ExtractData saying if CommandButton4 was clicked to this, if Command Button5 that... etc!!


Thats the last question promise!

Thanks a lot

Mike

[Bob Phillips]

You would need to store the button clicked, say in a module variabl, and test that in the procedure.

[lior03]

hello
i am trying to use xld's functions:
[VBA]
Private Sub Worksheet_Activate()
MsgBox " this workbook contain " & CountVisibleSheets() & " visible sheets " & vbNewLine & _
" this workbook contain " & CountnonVisibleSheets() & " nonvisible sheets "
End Sub
[/VBA]
how can i make excel to tell me there are no sheets hidden.by my code there is no way to know.it can't specify a zero.
another question- how can i hide all sheets in a workbook except the activesheet i am in.
thanks

[Bob Phillips]

[vba]

Private Sub Worksheet_Activate()
Dim nVisibleSheets As Long
Dim nHiddenSheets As Long
nVisibleSheets = CountVisibleSheets()
nHiddenSheets = CountnonVisibleSheets()
MsgBox "This workbook contains " & vbNewLine & _
vbTab & IIf(nHiddenSheets = 0, "no", nVisibleSheets) & " visible sheets, & " & vbNewLine & _
vbTab & IIf(nHiddenSheets = 0, "no", nHiddenSheets) & " non-visible sheets "
End Sub
[/vba]

[vba]
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> Activesheet.Name Then
sh.Visible = xlSheetHidden
End If
Next sh
[/vba]