Well NateO,
I thought I could make it work with the code you gave me, but im getting an error saying "Object variable or with block variable not set". What does this mean? Here is my full code for reference.
Sub SheetSelector()
Dim ChooseSheet As Worksheet
Dim CurrentBook As Workbook
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
Application.ScreenUpdating = False
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
ActiveWindow.SelectedSheets.Visible = False
Application.ScreenUpdating = True
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 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
'Defines selected sheet from check boxes and creates named ranges accordingly
CurrentBook = ActiveWorkbook.Name
ChooseSheet = Worksheets(cb.Caption).Value
ActiveWorkbook.Names.Add Name:="DATES", RefersToR1C1:= _
"=OFFSET('" & ChooseSheet & "'!R2C1,0,0,COUNTA('" & ChooseSheet & "'!C1),1)"
ActiveWorkbook.Names.Add Name:="LOANOFFICER", RefersToR1C1:= _
"=OFFSET('" & ChooseSheet & "'!R2C2,0,0,COUNTA('" & ChooseSheet & "'!C2),1)"
Range("B6").Formula = "=SUMPRODUCT(('" & CurrentBook & "'!DATES=B$5)*('" & CurrentBook & "'!LOANOFFICER=$H6))"
Range("DATA_RANGE").Select
Selection.FillRight
Range("FILLDOWN_RANGE").Select
Selection.FillDown
Range("B5").Select
End If
Next cb
End If
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
End Sub
Thanks for all your help, its greatly appreciated!