| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 .. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
include empty sheets
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" wrote in message ... 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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
include empty sheets
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" wrote in message ... 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 . . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
include empty sheets
For i = 1 To ActiveWorkbook.Sheets.Count
Change worksheets to Sheets in the code -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ... 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" wrote in message ... 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 . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
include empty sheets
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 wrote in message ... 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" wrote in message ... 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 . . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
include empty sheets
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 wrote in message ... 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 wrote in message ... 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" wrote in message ... 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 . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sort Excel 2007 to include empty row between data? | Excel Worksheet Functions | |||
| Don't include category X labels if row empty | Charts and Charting in Excel | |||
| How do I not include empty cell value chart labels in pie charts? | Charts and Charting in Excel | |||
| skip empty sheets | Excel Programming | |||
| Printing Sheets that only include data | Excel Programming | |||
Hybrid Mode
