Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling JJ re generate form at runtime
JJ,
I use the code below to generate a list of all the visible worksheets with a caption of the sheetname and a checkbox for each. The user then selects which sheets they want printed. You should easily be able to adapt for your requirements by changing the lines suffixed 'XXXXX ie Call mymacro This is from an earlier version of Excel and is therefore a dialogsheet rather than a form but it still works! Let ma know how you get on. Sub Printmenu() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet 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.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 ''''''''''''''''''''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 Application.DisplayAlerts = False Worksheets(cb.Caption).Activate 'XXXXX ActiveSheet.PrintOut 'XXXXX 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
generate a form at runtime? | Excel Programming | |||
Calling Outlook in Excel runtime error | Excel Programming | |||
how to generate a unique form # when using an excel form template | Excel Worksheet Functions | |||
Using For/Next Loop To Generate 4 CommandButtons On A UserForm Results In A Runtime Error 91 | Excel Programming | |||
Calling (Function?) from Userform_Initialize to generate list of unique values for Combo box | Excel Programming |