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

How about something like this:

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

fill in the blank



"Darren Hill" wrote:

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

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
On Tue, 03 Apr 2007 01:52:03 +0100, Barb Reinhardt
<(E-Mail Removed)> wrote:

> How about something like this:
>
> if lcase(oThis.Sheets(i).Name) like "vba_*" then ...
>
> fill in the blank
>
>
>
> "Darren Hill" wrote:
>
>> 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
>>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Instead of this

> >> For i = 1 To ActiveWorkbook.Worksheets.Count

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" wrote:

> 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
> On Tue, 03 Apr 2007 01:52:03 +0100, Barb Reinhardt
> <(E-Mail Removed)> wrote:
>
> > How about something like this:
> >
> > if lcase(oThis.Sheets(i).Name) like "vba_*" then ...
> >
> > fill in the blank
> >
> >
> >
> > "Darren Hill" wrote:
> >
> >> 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
> >>
>
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>

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

Thanks,

Darren

On Tue, 03 Apr 2007 11:24:02 +0100, Barb Reinhardt
<(E-Mail Removed)> wrote:

> Instead of this
>
>> >> For i = 1 To ActiveWorkbook.Worksheets.Count
>
> 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" wrote:
>
>> 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
>> On Tue, 03 Apr 2007 01:52:03 +0100, Barb Reinhardt
>> <(E-Mail Removed)> wrote:
>>
>> > How about something like this:
>> >
>> > if lcase(oThis.Sheets(i).Name) like "vba_*" then ...
>> >
>> > fill in the blank
>> >
>> >
>> >
>> > "Darren Hill" wrote:
>> >
>> >> 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
>> >>
>>
>>
>>
>> --
>> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

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

"Darren Hill" wrote:

> Thanks Barb, rewriting the code that way did the trick.
> I'm very grateful,
>
> Thanks,
>
> Darren
>
> On Tue, 03 Apr 2007 11:24:02 +0100, Barb Reinhardt
> <(E-Mail Removed)> wrote:
>
> > Instead of this
> >
> >> >> For i = 1 To ActiveWorkbook.Worksheets.Count
> >
> > 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" wrote:
> >
> >> 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
> >> On Tue, 03 Apr 2007 01:52:03 +0100, Barb Reinhardt
> >> <(E-Mail Removed)> wrote:
> >>
> >> > How about something like this:
> >> >
> >> > if lcase(oThis.Sheets(i).Name) like "vba_*" then ...
> >> >
> >> > fill in the blank
> >> >
> >> >
> >> >
> >> > "Darren Hill" wrote:
> >> >
> >> >> 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
> >> >>
> >>
> >>
> >>
> >> --
> >> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
> >>
>
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>