Deeds,
Here is a previous post of my that creates a custom dialog box for
worksheets. Adapt to suit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim iBooks 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
iBooks = 0
' Add the checkboxes
TopPos = 40
With ActiveWorkbook
For i = 1 To .Sheets.Count
If .Sheets(i).Name <> PrintDlg.Name Then
Set CurrentSheet = .Sheets(i)
' Skip empty sheets and hidden sheets
iBooks = iBooks + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(iBooks).Text = _
Sheets(i).Name
TopPos = TopPos + 13
End If
Next i
End With
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240
' Reactivate original sheet
CurrentSheet.Activate
PrintDlg.Visible = False
' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to process"
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
Application.ScreenUpdating = True
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
MsgBox Sheets(cb.Caption).Name & " selected"
End If
Next cb
Else
MsgBox "Nothing selected"
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Deeds said:
Hi,
I am trying to create a dialog box that comes up with radio buttons or
check boxes to print certain print areas, they click on one, and up comes
that chosen print area for preview before printing. Can someone help?
