I have a spreadsheet with 4 columns, 100 rows. At times, the content in Column B, for a specific row, needs to be printed to
a label printer. The user needs to be able to 'point-click-print' on a row in the spreadsheet to initiate printing.
My solution has an Option Button (radio button) adjacent to each row. When a button is selected, the user is
prompted to enter the number of printouts he would like. When he hits OK, only the content of (Column B) of the respective
row must print, and it should print as many times as the user requested in the prompt box.
I welcome ideas on how
to better address this need.
1. How do I pass the range of a cell as a variable from the
OptionButton click event to the PrintLabel module? (For example, when the user clicks on the OptionButton for Row 9, I want
the corresponding content in cell B9 to be printed). I'd like to only use one instance of the PrintLabel module and call on
it with different values (note, the code below is hard coded with B9, which is what I'd like to change to be more dynamic)
2. Despite capturing the user's desired number of copies, only one printout is generated. Any ideas on what I'm
3. If you enter a non-numeric in the field for number of printouts, it notifies you that a numeric is
required. After entering a numeric, hitting OK or Cancel generates the VB error "Run-time Error 1004 PrintOut method of
Range class failed" Any ideas why?
4. How would I be able to cap the number of printouts allowed to be entered in
the prompt box to no more than 25?
5. How can I adjust the test size of the printout?
6. How can I have
the printout contain the only the cell content, and not part of the header that is configured to print when the entire page
is printed? (see attached .pdf - all that's needed is the bottom text)
Thanks in advance!
[Sheet 3 (Data Entry)]
Private Sub OptionButton1_Click()
'Call PrintLabel module
' Ask user to input the number of copies to be printed.
Dim rngToPrint As Range
Dim Message, Title, Default, MyValue
Message = "Please enter the number of inventory labels to print"
' Set prompt.
Title = "Number of inventory labels"
' Set title.
Default = "1"
' Set default.
' Display message, title, and default value.
MyValue = vbNullString
MyValue = InputBox(Message, Title, Default)
If MyValue = vbNullString Then Exit Sub
If Not IsNumeric(MyValue) Then
MsgBox "Numeric entry only please"
Set rngToPrint = Sheets("Data Entry").Range("B9")
rngToPrint.PrintOut Copies:=MyValue, Collate:=True
'print range and number of copies requested
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines