Help with fairly advanced list/print macro.

[Guest]

Hi,

I have a workbook with several sheets (let's say about 30). The first is a
information sheet where I type the information to end up in certain cells on
sheets 3 through 30. Sheet number two is a summary sheet which is linked to
sheets 3-30 and summarizes information from them. In order to make these
summaries I have a few special formulas outside the printing range of sheets
3-30. On the summary page, I have a macro-assigned button that prints every
sheet in the workbook but leaves out the formulas that are outside the
printing range (it effectively edits all sheets to hide them, then print them
and show them again). Now, to my question:

I would like to do the following; create a drop-down list on the summary
page where I can choose (preferrably by ctrl-clicking) several of the sheets
in the workbook, or all of them. Then, I'd like to have a button that would
print the sheets I have selected as well as hide above mentioned formulas.
The latter I could probably incorporate myself, but the list-to-print I can
not. Also, all the sheets always have the same name and I do not tamper with
them while using the workbook in my work.

Does anyone have a clue of how to solve this? If you need any more info,
just ask.
Thanks.


Regards,
Olle Svensson

 

[Guest]

Olle,
Copy this code into your sheet. Rename the sheets in this code to fit your
particular spreadsheet. Create a button on your sheet you want to print from
and assign this macro to the button. This will create a pop up dialog box
that has all of your sheet names on it. Simply select the sheets you want to
print from the list. It creates the list "on the fly" so if you add/delete
sheets it will auto-update the list. It won't list off hidden sheets, so make
sure the ones you want to print are visible.

Sub a()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Dim bIsLeft As Boolean
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 = 30
bIsLeft = True
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
If bIsLeft Then
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5

Else
PrintDlg.CheckBoxes.Add 220, TopPos, 150, 16.5
TopPos = TopPos + 12
End If
bIsLeft = Not bIsLeft
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
End If
Next i

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(168, PrintDlg.DialogFrame.Top + TopPos - 30)
.Width = 415
.Caption = "Select pages to print"
End With

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 415

' 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 and return to main worksheet
CurrentSheet.Activate
Sheets("MySheet").Select
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
' ActiveSheet.PrintPreview 'for debugging
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If
CurrentSheet.Activate
Sheets("MySheet").Select
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete


End Sub

Cheers,
Tom

 

[Guest]

That was nothing less than a flawless macro. Thank you so much!

One thing though; it seems I am not as skilled as I thought with VBA and I
need to somehow incorporate my earlier macro (the one that hides certain
columns for print and then shows them afterwards) into yours. I gave it a
shot but only ended up with errors of the kind I do not understand. If you
(or anyone who knows these things) could help me, I would be very grateful.
The macro I want to incorporate is the following:

Sub ReadyForPrint()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Range("w:ac").EntireColumn.Hidden = True
Next wks

ActiveWorkbook.PrintOut Copies:=1, Collate:=True 'save paper for testing

For Each wks In ActiveWorkbook.Worksheets
wks.Range("w:ac").EntireColumn.Hidden = False
Next wks

End Sub

Maybe there is a more flexible solution than what I used to use, but I want
it to go together with the macro provided below.

Thanks,
Olle

 

[Guest]

Olle,
Insert this code into the macro (before and after the print job is
executed)- change the sheet names/columns to match what you need for your
sheets.

'To hide columns before printing
Sheets("MyWorksheet").Select
Columns("X:X").Select
Selection.EntireColumn.Hidden = True
Sheets("MySheet").Select
Range("A1").Select

'To unhide columns after printing
Sheets("MyWorksheet").Select
Columns("X:X").Select
Selection.EntireColumn.Hidden = False
Sheets("MySheet").Select
Range("A1").Select

 

[Guest]

Thanks, that seems to work just as I wished. Again, thanks!

Regards,
Olle