Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob Phillips' Common Dialog problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob Phillips' Common Dialog problem
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob Phillips' Common Dialog problem
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 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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob Phillips' Common Dialog problem
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 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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob Phillips' Common Dialog problem
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 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 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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bob Phillips' Common Dialog problem
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 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 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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Common Dialog Box | Excel Programming | |||
common dialog box | Excel Programming | |||
Common Dialog & other | Excel Programming | |||
Common Dialog Boxes | Excel Programming | |||
Common Dialog Box | Excel Programming |