include empty sheets

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

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

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

For i = 1 To ActiveWorkbook.Sheets.Count

Change worksheets to Sheets in the code


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



<[email protected]> wrote in message news:[email protected]...
> 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]...
>>> 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
>>> .
>>>
>>>
>>
>>
>>.
>>

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


>-----Original Message-----
>For i = 1 To ActiveWorkbook.Sheets.Count
>
>Change worksheets to Sheets in the code
>
>
>--
>Regards Ron de Bruin
>http://www.rondebruin.nl
>
>
>
><[email protected]> wrote in message
news:[email protected]...
>> 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]...
>>>> 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
>>>> .
>>>>
>>>>
>>>
>>>
>>>.
>>>
>
>
>.
>

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

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



<[email protected]> wrote in message news:[email protected]...
> 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
>
>
>>-----Original Message-----
>>For i = 1 To ActiveWorkbook.Sheets.Count
>>
>>Change worksheets to Sheets in the code
>>
>>
>>--
>>Regards Ron de Bruin
>>http://www.rondebruin.nl
>>
>>
>>
>><[email protected]> wrote in message
> news:[email protected]...
>>> 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]...
>>>>> 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
>>>>> .
>>>>>
>>>>>
>>>>
>>>>
>>>>.
>>>>
>>
>>
>>.
>>