Solved: Counting Visible Sheets in a workbook

[itipu]

Guys, I have this code

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

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]

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

[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



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

[mdmackillop]

I still think this would be simpler with a Userform :devil2:

[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



Public HowClosed As String

and in frmExtract add this procedure code



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

and change the extract procedure to



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("A1:D1").EntireColumn.AutoFit
Unload frmExtract

End With

End If

End Sub

[lior03]

hello
i am trying to use mathewpatrick function to find the opposite- namely how many hidden sheets there are in the activeworkbook.

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

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.

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
what is wrong ?
thanks



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

[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:

Private Sub Worksheet_Activate()
MsgBox " this workbook contain " & CountVisibleSheets() & " visible sheets " & vbNewLine & _
" this workbook contain " & CountnonVisibleSheets() & " nonvisible sheets "
End Sub

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]

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



Dim sh As Worksheet

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

[lior03]

dear xld
what i meant was that when there are no hidden sheets in the workbook
i get a message like " activewotkbook contains nonvisible sheets".
i want excel to use zero (0) or the word no in describing how many nonvisible there are.

[Bob Phillips]

Isn't that what I gave you?

[lior03]

hello
i wanted to hide all sheets in a workbook that contain text.
Sub checktext()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If Len(Range("A5")) > 5 Then
sh.Visible = xlSheetVeryHidden
MsgBox sh.name, vbExclamation + vbOKOnly
End If
Next
End Sub

then i came up with the idea of counting the sheets

Function textA5(Optional Wb As Workbook)
Dim sh As Worksheet
If IsMissing(Wb) Then Wb = ActiveWorkbook.name
For Each sh In ActiveWorkbook.Worksheets
If Len(Range("A5")) > 5 Then
textA5 = textA5+1
End If
Next
End Function

what is wrong with my function?