How to write a macro to print data pertaining to a list box?

fred · Apr 10, 2012

Hi all,

I have created a list box (form control) in excel with a cell link to C3. As I click on the different employee name on the list box and C3 (the counter) would change from 1 to 500 (I only have actual data from 1 to 250, though. I just want to make sure I have enough space for future expansion of names).

At the same time, area B10:K47 would show up different info depending on the name I click. This area is also set for print. (all the data within B10:K47 would come from an "Input" worksheet).

Question:

1. I do not want to manually click 250 names one at a time and hit the print button in Excel. Is there a way I can set up a macro so that the macro would choose from the first name and send it to print, then choose the second name on the list and send it to print and stop at 250 but not 500?

2. what is the best way to select all the names between 95th and 160th (inclusive) and send it to print?

I think I only need one macro because all I should do is choose 1 to 250 if I want to print all, or 95th to 160th (as an example) if I only want to print certain large range of names.

I don't mind manually clicking and printing 5 or 10 or even 15 names at a time. But more than that would be too labor heavy.

Thank you very much.

SirJB7 · Apr 10, 2012

Hi, fred!

Assuming your user form has a control named ListBox1 referring to the employee data, add a command button named CommandButton1 to the form and copy this code into its VBA window.

-----

[pre]

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim I As Integer, J As Integer
I = 2
With UserForm1.ListBox1
Do Until I - 2 &#62;= .ListCount
.ListIndex = I - 2
For J = 1 To .ColumnCount
.BoundColumn = J
Debug.Print .Value,
Next J
Debug.Print
I = I + 1
Loop
End With
End Sub

[/pre]
-----

This snippet will let you retrieve the required employee data for each person. Just replace the "Debug.Print ..." statements by the proper instructions (building a list in a helper worksheet, for example).

Regards!

SirJB7 · Apr 10, 2012

Hi, fred!

Reading again your post, I think you might just replace the "For.." group and the following "Debug.Print..." by an "ActiveWorkbook.PrintOut", as you have already set a properly formatted print area, if I didn't misunderstand again

So vary the .ListIndex property and print.

Regards!

fred · Apr 10, 2012

Hi SirJB7,

First of all, thanks for helping me. I have encounter a problem with "UserForm1". There is an error message : Compile error: Variable not define

I'm using Excel 2010. Does that make a difference?

SirJB7 · Apr 10, 2012

Hi, fred!

My mistake, sorry, I read "user form" at your very first line where it says "form control". Change the code to this:

-----

[pre]

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim I As Integer, J As Integer
I = 2
With Range(&#34;List1&#34;)
Do Until .Cells(I, 1).Value = &#34;&#34;
Range(&#34;C3&#34;).Value = .Cells(I, 1).Value
ActiveWorksheet.PrintOut
I = I + 1
Loop
End With
End Sub

[/pre]
-----

Where List1 is a named range that refers to the column that holds the values for C3 cell.

Regards!

fred · Apr 10, 2012

I'll try and see how it works tonight. Many thx, SirJB7.

SirJB7 · Apr 10, 2012

Hi, fred!

You're welcome. Just advise if any issue arises.

Regards!

fred · Apr 11, 2012

Hi SirJB7,

Good morning. I tried last night but there is a compile error: Variable not defined on

ActiveWorksheet.

I thought the solution would be macro asking me which person to start printing (such as 67th person) and ends at which person (such as 189th person).

Also, I look up the "Private Sub" but still don't understand why we will need a Private sub in this macro.

Could you please kindly assist? Thank you very much.

SirJB7 · Apr 11, 2012

Hi, fred!

The Private is automatically added by VBA for the command button click event (it was intended to add a button as I stated in my first answer).

What Excel version are you using? Mine's 2010 but I don't think that the PrintOut method changed from previous version.

The macro a priori checks all non-blank cells in column, so if you want to control from/to, you'll have to add a pair of InputBox statements or reference two cells from within the macro code (replacing the initial I=2 by an I=<from_variable> and changing the condition of the Until).

Can you please upload the related file?

Regards!

fred · Apr 11, 2012

hi SirJB7

I have uploaded a file. The file name is Testing.xlsm and I'm using MS2010 now. Finally upgrading from 2007 to 2010 version.

https://docs.google.com/open?id=0B1Yt3Yl95LuPS2xtSF96bzE2SGM

SirJB7 · Apr 11, 2012

Hi, fred!

Give a look to this file:

http://dl.dropbox.com/u/60558749/How%20to%20write%20a%20macro%20to%20print%20data%20pertaining%20to%20a%20list%20box_%20-%20Testing%20%28for%20fred%20at%20chandoo.org%29.xlsm

It does the job, but I don't know if the interface is suitable for you.

If you don't want 2 listbox controls, you can keep the second one, add a new button for selecting (or adding code for the change event, and I think it's all.

Just advise how does this go on.

Regards!

fred · Apr 11, 2012

Thank you very much, SirJB7! It's absolutely BEAUTIFUL! I'm sure many people here could use this technique generating individual report without creating one worksheet for one employee.

I used the normal listbox because it was easy for me to use and my manager can view a particular employee when he is in Excel.

I have no problem using the Active X control but I don't know how to make the list show up on the 2nd listbox. I was also lost when you mention "commandbutton", I thought you mean I go insert a square shape and use it as a commandbutton. So we need 3 macros to make this work, huh? I'll need time to study this.

Once again. Thank you very much!!

fred · Apr 13, 2012

Hi SirJB7!

if it is not too much trouble, could you please kindly explain the flow of the macros you have created?

Private Sub lstPrinting_LostFocus()

Dim I As Integer, J As Integer

J = 0

With lstPrinting

For I = 0 To .ListCount - 1

If .Selected(I) = True Then J = J + 1

Next I

End With

Range("G3").Value = J

End Sub

Private Sub cmdPrintSelected_Click()

Dim I As Integer, J As Integer

With lstPrinting

For I = 0 To .ListCount - 1

If .Selected(I) = True Then

Range("C3").Value = I + 1

ActiveSheet.PrintOut

End If

Next I

End With

End Sub

Private Sub CommandButton1_Click()

Dim I As Integer, J As Integer

I = 2

With Range("List1")

Do Until .Cells(I, 1).Value = ""

Range("C3").Value = .Cells(I, 1).Value

ActiveSheet.PrintOut

I = I + 1

Loop

End With

End Sub

I also realized the naming of the listbox and the commandbox. but i don't quite understand how it works when you added "_LostFocus", "_Click" on the private sub lines. And I realized once I have selected multiple pages to print, it will over-write the one I'm viewing and print until the last one to print show up.

I wonder if we can add a line so that it will go back to the last one I view? It's minor problem so if it is too troublesome to fix, don't worry about it. It is perfect as now.

SirJB7 · Apr 13, 2012

Hi, fred!

Just coming back, let's see...

First, how to make the list show up on the 2nd listbox: property ListFillRange, assign value unquoted "Input!$B$4:$B$500".

Second, commandbutton: ActiveX command button control, not rectangular shaped with macro assignment.

Third, we need 3 macros to make this work: we need two controls (command button and listbox) and in this case we only need to handle one event per control, so we actually need 2 macros; I left the first one (for CommandButton1 click event) just for pointing to my mistake, if you look at it in detail it has become the cmdPrintSelected event code with the adjustments needed for the new scenario.

Fourth, flow of the macros: it isn't so much trouble, the biggest issue is that I'm growing old and becoming lazier... but I'll try.

a) 1st macro

[pre]

Code:

Private Sub CommandButton1_Click()
Dim I As Integer, J As Integer
I = 2
With Range(&#34;List1&#34;)
Do Until .Cells(I, 1).Value = &#34;&#34;
Range(&#34;C3&#34;).Value = .Cells(I, 1).Value
ActiveSheet.PrintOut &#39; my mistake here, it isn&#39;t ActiveWorksheet
I = I + 1
Loop
End With
End Sub
It was the macro for the previous option, that where you printed all the employees as you first had requested.

It fills C3 cell with the employee number for each row starting from 2 (because of titles) and then printed current sheet.


b) 2nd macro

[pre][code]Private Sub lstPrinting_LostFocus()
Dim I As Integer, J As Integer
J = 0
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then J = J + 1
Next I
End With
Range(&#34;G3&#34;).Value = J
End Sub

[/pre]
It's a macro that operates when new control lstPrinting loses focus, i.e., when another object or cell is selected.

It isn't a mandatory macro, it's only an information macro that fills G3 cell with the number of selected items in lstPrinting list box (which has the MultiSelect property set to multi-select extended for using shift end operations).

The goal is to let the user know how many records he's going to print.

It cycles sequentially all the items in the listbox (which range from zero to ListCount property - 1) and if it's selected (property Selected(i)=true) it counts that item, and after all elements in list have been checked, it places in C3 cell the total number of selected items.

c) 3rd macro

Private Sub cmdPrintSelected_Click()
Dim I As Integer, J As Integer
With lstPrinting
For I = 0 To .ListCount - 1
If .Selected(I) = True Then
Range("C3&#34

.Value = I + 1
ActiveSheet.PrintOut
End If
Next I
End With
End Sub[/code][/pre]
It's the only needed macro, the one which does the job.

It's triggered when you click on command button, supposedly after having selected desired items and checking it's count in G3 cell.

It cycles sequentially all list items and for each one that is selected (Selected property equal true) it places the ordinal number (index of sequential cycling + 1) in cell C3 (so the sheet will get populated with data for employee "C3"-valued, and calls the PrintOut method (aka as printing by default) for current sheet.

Hope it helps you.

Regards!

EDIT:

One of the most widely adopted standard naming conventions for controls in Visual Basic (and extended for VBA) say that you should use a prefix that identifies the type of control (cmd for command button, lst for list box, txt for text box, lbl for label, ...). It's only a recommendation, a strong one, but optional at last: you can name your controls as John, Dog, AX123Jy, PinkElephant, but it's less clear. The best practices indicate that you should follow standard naming, yet the first described, another one, or any established by yourself.

It's up to you to write a clear and universally understandable code, or DOC code, i.e., deliberately obfuscated code: for what exists international contests too!... see this link: http://en.wikipedia.org/wiki/International_Obfuscated_C_Code_Contest ... isn't it a masterpiece? Aesthetically speaking of course.

The unchangeable naming rules for events used by VBA identifies each macro for each event for each control, adding a suffix to the name control which begins with an underscore and it's followed by the name of the event. For example the name of the subroutine (more proper than calling it macro) for the mouse click event for the command button control cmdPrintSelected will be named cmdPrintSelected_Click. You can see each event available for each control and its eventual arguments, selecting a control from the left drop-down list at the top of the code pane and then displaying (clicking) in the right drop-down list at the same place. The combination <control name>_<event> is fixed, can't be changed and the Private Sub associated must be placed in the same sheet that holds the control (not in ThisWorkbook, except for workbook events, not in modules).

fred · Apr 13, 2012

Wow! Thank you very much, SirJB7! I can't tell if you are old. but i can say you are definitely not lazy helping us out here at chandoo.org.

SirJB7 · Apr 13, 2012

Hi, fred!

Thanks for your kind words. And as a matter of fact I'm very young... still... just half a century plus one

Regards!

How to write a macro to print data pertaining to a list box?

fred

Member

SirJB7

Excel Rōnin

SirJB7

Excel Rōnin

fred

Member

SirJB7

Excel Rōnin

fred

Member

SirJB7

Excel Rōnin

fred

Member

SirJB7

Excel Rōnin

fred

Member

SirJB7

Excel Rōnin

fred

Member

fred

Member

SirJB7

Excel Rōnin

fred

Member

SirJB7

Excel Rōnin