skip empty sheets

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I am using a script downloaded from a web page the code
below is missing out my charts from the list of results
due to the skip empty sheets part. Please can someone
tell me how to take it out so the charts are part of the
list!

Thanks

' 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
 
Can you restate or explain what you mean by:

"the code below is missing out my charts from the list of results
due to the skip empty sheets part."
 
I believe he means that if his sheet contains only charts, then the line:

If Application.CountA(CurrentSheet.Cells) <> 0
considers it to be "empty" and does not make a checkbox for that sheet. I
would think he should add another test for Objects.Count <>0

If Application.CountA(CurrentSheet.Cells) <> 0 _
And CurrentSheet.Objects.count <>0 _
And CurrentSheet.Visible Then
.... 'make checkboxes
End If

Mike F
 
Back
Top