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...
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
523
Hi Yolki, welcome to the board.

Try this link from John Walkenbach. I've used it many times.
 

Yolki

New Member
Joined
Aug 17, 2009
Messages
3
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.
 

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
523
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
 

Yolki

New Member
Joined
Aug 17, 2009
Messages
3
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:

TinaP

Well-known Member
Joined
Jan 26, 2005
Messages
523
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
 

joogi

New Member
Joined
Sep 23, 2009
Messages
2
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.
 

joogi

New Member
Joined
Sep 23, 2009
Messages
2
Can someone help me with this? I am new to the forum.... Thanks.
 

Prashant1211

New Member
Joined
Jun 9, 2020
Messages
5
Office Version
2016
Platform
Windows
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
 

Prashant1211

New Member
Joined
Jun 9, 2020
Messages
5
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,100,039
Messages
5,472,119
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top