include empty sheets

[Rob]

The following code is used in my sheet as part of a
larger code to make a list on a dialogue of all sheets in
the workbook. I have seen the part of code saying "skip
empty sheets" and have come to the conclusion this is why
the code is missing my charts from the list when I run it.

How can I get rid of this part to allow for the charts to
show?

Many Thanks - I have been messing around with this for
hours.

Rob

Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0
And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240
..

 

[Ron de Bruin]

Hi Rob

You can use this instead
If CurrentSheet.Visible Then

But the code use Worksheets
For i = 1 To ActiveWorkbook.Worksheets.Count
This will only count the worksheets and not chart sheets if you have them

 

[Guest]

hi ron,

Thanks

I have altered the code, it has half worked as you said
but how can I include charts i guess i'm wrong here

TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
Set CurrentSheet = ActiveWorkbook.Charts(i)
' Skip hidden sheets
If CurrentSheet.Visible Then
SheetCount = SheetCount + 1

-----Original Message-----
Hi Rob

You can use this instead
If CurrentSheet.Visible Then

But the code use Worksheets
For i = 1 To ActiveWorkbook.Worksheets.Count
This will only count the worksheets and not chart sheets if you have them


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rob" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

For i = 1 To ActiveWorkbook.Sheets.Count

Change worksheets to Sheets in the code

 

[Guest]

yes I have altered it but now the code in the 4th line is
yellow in the debugger?

im sure it is right!!

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Sheets.Count
Set CurrentSheet = ActiveWorkbook.Sheets(i)
' Skip hidden sheets
If CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

 

[Ron de Bruin]

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Sheets.Count
Set CurrentSheet = ActiveWorkbook.Sheets(i)
' Skip empty sheets and hidden sheets
If CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

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

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Sheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Activate
End Sub

 

[Guest]

That is great I can now see the charts aswell.

I now have the problem of being able to see a rogue
worksheet everytime called dialogue1 or dialogue# which I
dont want the name is different every time so how do I
hide the window from itself?

I am really sorry because I didnt know this was going to
drag on, i appreciate your help.

rob