Bob Phillips' Common Dialog problem

[Darren Hill]

I'm trying to use Bob Phillips Hide Selected Sheet macro to show a list of
sheets, then check boxes to show or hide them.
The default macro works okay. the problem I'm having is that I'm trying to
exclude sheet names that start with "VBA_". When I run the macro, two
"VBA_" sheets do show up, and two that aren't named that way don't, so two
(or more) sheets aren't being identified properly.

The macro is below. My single line change is between the lines of equal
signs (=============)

Thanks, Darren
----------------------------------
Option Explicit

'---------------------------------------------------------------------
' Function: Hide Selected sheet(s)
' Uses 'Common Dialog' technique
' Synopsis: Builds a print dialog with a list of worksheets and
' a checkbox.
' If OK, the checked sheet(s) are then hidden.
' Finaly dialog is cleared down
'---------------------------------------------------------------------
' Author: Bob Phillips - © 2004 xlDynamic.com
' Based on an idea and original code by John Walkenbach
'---------------------------------------------------------------------

Dim fCancel As Boolean

'---------------------------------------------------------------------
Public Sub CDTSheetHide()
'---------------------------------------------------------------------
Const sTitle As String = "Selected Sheet Hide"
Const sMsgTitle As String = "Sheet Hide"
Const sID As String = "VBA_SheetHide"
Dim dlgThis As DialogSheet
Dim oThis As Workbook
Dim CurrentSheet As Worksheet
Dim oCtl As CheckBox
Dim SheetCount As Long
Dim nBinary As Long
Dim cMaxLetters As Long
Dim i As Long
Dim j As Long
Dim TopPos As Long

Application.ScreenUpdating = False

Set oThis = ActiveWorkbook

If oThis.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical, sMsgTitle
Exit Sub
End If

Set CurrentSheet = ActiveSheet
Set dlgThis = oThis.DialogSheets.Add
With dlgThis

.Name = sID
.Visible = xlSheetHidden

SheetCount = 0

'Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
'============================================
' MY CHANGE: modify to not include VBA_sheets
'============================================
If Left(oThis.Sheets(i).Name, 4) <> "VBA_" Then
'If oThis.Sheets(i).Visible <> xlVeryHidden Then
'============================================
' END MY CHANGE (apart from End If to close statement)
'============================================
'calculate length of longest sheet name

Debug.Print oThis.Sheets(i).Name
If Len(oThis.Sheets(i).Name) > cMaxLetters Then
cMaxLetters = Len(oThis.Sheets(i).Name)
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
'Skip empty sheets sheets
SheetCount = SheetCount + 1
.CheckBoxes.Add 78, TopPos, 150, 16.5
.CheckBoxes(SheetCount).Text = CurrentSheet.Name
If Worksheets(i).Visible <> xlSheetVisible Then
.CheckBoxes(SheetCount).Value = True
End If
TopPos = TopPos + 13
End If
Next i

'position the CheckBoxes and buttons according to
' length of longest sheet name
.CheckBoxes.Left = 78
.Buttons.Left = 132 + (cMaxLetters * 4) + 10 + 24 + 8

'adjust dialog to align with number of controls and
' length of longest sheet name
With .DialogFrame
.Height = Application.Max(68, .Top + TopPos - 34)
.Width = 132 + (cMaxLetters * 4) + 10 + 24 + 8 - 10
.Caption = sTitle
End With

'change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

.Buttons("Button 3").OnAction = "CancelButton"


'Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If .Show Then
For Each oCtl In .CheckBoxes
If oCtl.Value = xlOn Then
Sheets(oCtl.Caption).Visible = xlSheetHidden
Exit For
End If
Next oCtl
End If
Else
MsgBox "All worksheets are empty."
End If

Application.DisplayAlerts = False
.Delete

End With

End Sub

Private Sub CancelButton()
fCancel = True
End Sub

 

[Barb Reinhardt]

How about something like this:

if lcase(oThis.Sheets(i).Name) like "vba_*" then ...

fill in the blank

 

[Darren Hill]

I'd never encountered the LIKE keyword before, so thanks for that.

When I run the macro with your statement, it shows lots of VBA sheets, but
also includes two named "Front Page" and "ToDoList"
When I run it with a NOT operator, which is what i need, it shows the same
list as before - the sheets "VBA_BlankRecord" and "VBA_SettlementIncome"
show up in the list and two others that don't include the VBA) string
don't.

It looks to me that the sheets(i) statement is actually getting
sheets(i+1) or (i-1), I'm not sure which.

Thanks,
Darren

 

[Barb Reinhardt]

Instead of this

I prefer to use
Dim aWS as worksheet
'Refer to worksheet name using aWS
For each aWS in activeworkbook.worksheets
Debug.print aws.name, aws.codename
next aws

 

[Darren Hill]

Thanks Barb, rewriting the code that way did the trick.
I'm very grateful,

Thanks,

Darren

 

[Barb Reinhardt]

You're quite welcome. I've learned a lot from these newsgroups myself.