|
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 .. |
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 . |
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 . . |
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 . . |
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 . . . |
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 . . . |
include empty sheets
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 -----Original Message----- 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 news:276a01c50c28$86e69250 ... 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 . . . . |
| All times are GMT +1. The time now is 05:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com