macro to print Excel sheets based on list box selection

Yolki

New Member
Joined
Aug 17, 2009
Messages
3
I am new here and this is my first post. I have reviewed, searched, and read the forums for this issue and cannot find it. I apologize if this issue is already answered somewhere and please send me a link if you know where the answer resides.

I have an Excel 2007 workbook where users complete a form (sheet1) and I need a macro (or code segment that I can include in my other macros) that will print the second, third, or fourth sheet (these are form letters contining the information entered into the form) based on the value of a combo list box ("Drop Down 2") which is one of the data entry values on the form. Right now, sheets 2 ,3, &4 are Visible and the users must decide which to select and print, but I'd prefer they remain hidden.

For example, if:
  • the value of "Drop Down 2" = 1 9default), then print nothing
  • the value of "Drop Down 2" = 2, then print sheet 2
  • the value of "Drop Down 2" = 3, then print sheet 3
  • the value of "Drop Down 2" = 4, then print sheet 4
to make this easier on myself, the list values 2,3 & 4 are the same as sheet names 2, 3, & 4.

I already have macros for clearing the form, protecting everything, and 'stamping' the data into a database of all previosly entered data, so perhaps the commands I need can simply be added to the "submit Data" macro.

Many thanks for reading...
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi Yolki, welcome to the board.

Try this link from John Walkenbach. I've used it many times.
 
Upvote 0
TYVM, TinaP...

This looks very interesting and I appreciate your reading my post. This gives them a cleaner interface to print the pnes they want, but I still might hold out for a way to print ONLY the ones i want them to print.
 
Upvote 0
I'm not sure I completely understand, but lack of knowledge has never stoppen me in the past...

If there are sheets that you don't want them to print, all you have to do is insert an If...Then statement so that a sheet name will not appear in the menu.
Code:
' Add the checkboxes
  TopPos = 40
  For i = 1 To ActiveWorkbook.Worksheets.Count
    Set CurrentSheet = ActiveWorkbook.Worksheets(i)
[COLOR=red]   If CurrentSheet.Name <> "Foo" Then[/COLOR]
      ' 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
[COLOR=red]   End If[/COLOR]
  Next i
 
Upvote 0
This is a great snippet and I have seen others like this before.


I also am making a carrer (EDIT: career) out of a lack of knowledge, but this request is for help with this issue:
  • While completing Sheet 1 of an Excel workbook (application for Financial Assistance), my users select one of 4 values in a combo list box among many ther data entry values
  • After they are done with all the data entry, I'd like to add a macro button for them to click that will print only one of the following sheets, which contain 'award letters' to the applicants, where sheet 2 matches list box choice 2 and the same with sheet 3--> list choice 3, etc.
  • I have the macro completed to stamp the data into a DB, clear the forms without saving, and to protect everything.
Thanks agian for your attention...
 
Last edited:
Upvote 0
OK, It sounds like you're pretty proficient with macros so I'll just skip to a quick macro that I pounded out. I didn't test it much, so make sure you save your workbook before testing. That said, I don't think it will cause any problems, either, it's pretty basic.

Since you didn't tell me how the combobox was inserted into Sheet1, I'll leave it to you to determine how to add SheetNumber to the macro. If you need help with it, just let me know.

I also assumed that the user would just click the print button to activate it. Again, let me know if you don't want it this way.

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim PrintSheet As Worksheet
Dim SheetNumber As Long
  Set PrintSheet = ActiveWorkbook.Sheets("Sheet" & SheetNumber)
  PrintSheet.PrintOut
End Sub
 
Upvote 0
Hi,

I also found J.Walk's VBA above very useful. Since I am a total novice on macros/vba, I need some helps to adapt this vba code.

How do I add two buttons (Select All and Deselect All checkboxes) to the dialog sheet? I want to have the option to select all checkboxes at once if I have 10 (out of 12) sheets to print. I want to be able to check "select all" first then deselect manually the 2 sheets that I don't want to print. It is more efficient if I have, for example, 20 sheets to print; I don't want to manually check every single one :)

This is almost like the checkboxes on a pivot table filter where you can select all or deselect all at your discretion.

thanks.
 
Upvote 0
Hello Everyone, I hope you are in good health.

I have a listbox at sheet1(named as "main sheet") which show all the sheets in the workbook to print. below is the code working fine but I do not want to show sheets (main sheet, summary, list) in the listbox for printing. Can anyone please help me and suggest how to code need to be modified to make it possible.

Many thanks for your help and i wish you a good day ahead :)

Sub print_sh()
On Error Resume Next
Dim i As Long, c As Long
Dim SheetArray() As String
On Error Resume Next
With ActiveSheet.ListBoxsh
For i = 0 To .ListCount - 1

If .Selected(i) Then

ReDim Preserve SheetArray(c)
SheetArray(c) = .List(i)

c = c + 1
End If


Next i
End With

Sheets(SheetArray()).PrintPreview
'Sheets(SheetArray()).PrintOut

End Sub
 
Upvote 0
very Sorry. I posted the incorrect code ;(

correct code is below and this call all sheets in workbook. can anyone suggest what changes has to be done to hide few sheets. Thanks

Private Sub Worksheet_Activate()
Dim Sh

Me.ListBoxsh.Clear
For Each Sh In ThisWorkbook.Sheets
Me.ListBoxsh.AddItem Sh.Name


Next Sh
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top