Print Selected Items in Excel Order Form

[B. Costa]

Hi,
I’m new to the site and also to Visual Basic. I have found the following
code from Dave Peterson on http://www.contextures.com/xlForm03.html, which
does more or less what I want apart from one thing, which I can’t figure out
how to change. Values from the worksheet list, starting in Column B and
moving right, are copied to the order form, into the addresses specified.
Instead of copying values from Column B and moving right (as in B1, C1,
D1...), I would like to copy to the form only selected cells like B1, D1,
H1...

Print Marked Items Code

Option Base 0
Sub PrintUsingDatabase()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant
Dim lOrders As Long

Set FormWks = Worksheets("Order Form")
Set DataWks = Worksheets("Orders")

myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")

With DataWks
'first row of data to last row of data in column B
Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'if the row is not marked, do nothing
Else
.Offset(0, -1).ClearContents 'clear mark for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value
Next iCtr
Application.Calculate 'just in case
'after testing, change to Preview to False to Print
FormWks.PrintOut Preview:=True
lOrders = lOrders + 1
End If
End With
Next myCell

MsgBox lOrders & " orders were printed."

End Sub

 

[Dave Peterson]

This retrieves the values from the cells based on the offset from column B.

myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")
myOffsets = Array(0, 1, 3, 5, 9, 15, 33)

So column B (offset 0) goes to E5
column C (offset 1) goes to E6
column E (offset 3) goes to B10
...
Column AI (offset 33) goes to D16



Option Base 0
Option Explicit
Sub PrintUsingDatabase()
Dim FormWks As Worksheet
Dim DataWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
Dim myAddresses As Variant
Dim myOffsets As Variant 'from column B
Dim lOrders As Long

Set FormWks = Worksheets("Order Form")
Set DataWks = Worksheets("Orders")

myAddresses = Array("E5", "E6", "B10", "E25", "B16", "C16", "D16")
myOffsets = Array(0, 1, 3, 5, 9, 15, 33)

If UBound(myOffsets) <> UBound(myAddresses) Then
MsgBox "Design error!"
Exit Sub
End If

With DataWks
'first row of data to last row of data in column B
Set myRng = .Range("B3", .Cells(.Rows.Count, "B").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
If IsEmpty(.Offset(0, -1)) Then
'if the row is not marked, do nothing
Else
.Offset(0, -1).ClearContents 'clear mark for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, myOffsets(iCtr)).Value
Next iCtr
Application.Calculate 'just in case
'after testing, change to Preview to False to Print
FormWks.PrintOut Preview:=True
lOrders = lOrders + 1
End If
End With
Next myCell

MsgBox lOrders & " orders were printed."

End Sub

 

[B. Costa]

Hi Dave
Never thought I would get the reply from the man himself, I have tried the
below and it works brilliantly, thanks for the quick reply and for writing
the code in the first place.

Kind Regards
Bruno

 

[Dave Peterson]

Glad it worked for you.

Hi Dave
Never thought I would get the reply from the man himself, I have tried the
below and it works brilliantly, thanks for the quick reply and for writing
the code in the first place.

Kind Regards
Bruno

 

[Boe]

I was trying to use this exact same code for my work but i run into
problem which i do not know how to solve it.

I want to print 2 forms on one page so i add "myAddress2" to the code.
But i cannot use a second Lbound and Ubound so i change it into

.Offset(0, -1).ClearContents 'clear mark for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value

.Offset(1, -1).ClearContents
iCtr2 = LBound(myAddresses2)
FormWks.Range(myAddresses2(iCtr)).Value _
= myCell.Offset(1, iCtr).Value

I have no idea how to write a VBA so i try to change here and there bu
it didn't work for me this time. I really need your help. Thank you

 

[Dave Peterson]

A lot of the procedure depends on how your data is laid out.

If you put your addresses for the "second" form on the same row, then you don't
need the second myAddresses2 variable. You can just keep going to the right of
the input row and add more addresses to the myAddress variable (in that
"myaddresses = Array(...)" statement).

Think of it as just printing a single form with double the information.

If this doesn't help, I think you'll have to explain what you did--to the table
and the userform.

I was trying to use this exact same code for my work but i run into a
problem which i do not know how to solve it.

I want to print 2 forms on one page so i add "myAddress2" to the code.
But i cannot use a second Lbound and Ubound so i change it into

Offset(0, -1).ClearContents 'clear mark for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value

Offset(1, -1).ClearContents
iCtr2 = LBound(myAddresses2)
FormWks.Range(myAddresses2(iCtr)).Value _
= myCell.Offset(1, iCtr).Value

I have no idea how to write a VBA so i try to change here and there but
it didn't work for me this time. I really need your help. Thank you.

--
Boe
------------------------------------------------------------------------
Boe's Profile: 1465
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165649

Microsoft Office Help

 

[Dave Peterson]

ps. I didn't mean userform--I meant form on the other worksheet.

I was trying to use this exact same code for my work but i run into a
problem which i do not know how to solve it.

I want to print 2 forms on one page so i add "myAddress2" to the code.
But i cannot use a second Lbound and Ubound so i change it into

Offset(0, -1).ClearContents 'clear mark for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value

Offset(1, -1).ClearContents
iCtr2 = LBound(myAddresses2)
FormWks.Range(myAddresses2(iCtr)).Value _
= myCell.Offset(1, iCtr).Value

I have no idea how to write a VBA so i try to change here and there but
it didn't work for me this time. I really need your help. Thank you.

--
Boe
------------------------------------------------------------------------
Boe's Profile: 1465
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165649

Microsoft Office Help

 

[Boe]

Thank you for your quick respond Dave.

What i mean with 2 form is that. my form is so small i can save pape
with printing 2 of them on the same paper.

How would i go about and do that?
Thanks.

 

[Simon Lloyd]

*Why not add a workbook?*
Providing a workbook will not only get you your answer quicker but wil
better illustrate your problem, usually when we can see your data (-i
can be dummy data but must be of the same type-) and your structure i
is far easier for us to give you a tailored, workable answer to you
query :

Thank you for your quick respond Dave

What i mean with 2 form is that. my form is so small i can save pape
with printing 2 of them on the same paper.

How would i go about and do that
Thanks.

*------------------------------- posted before receiving a reply afte
22 minutes of original post -------------------------------

If i understand your suggestion correctly, i will need to put mor
array into "myAddresses" right

but wouldn't that means i cannot mix and match them? (when i order t
print one data, the other one is fix to it

is there other ways around this
Thank you

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Dave Peterson]

If your data is laid out the same way as Debra's site shows, then you'd need to
loop through each pair of rows.

But you didn't answer my question.

Thank you for your quick respond Dave.

What i mean with 2 form is that. my form is so small i can save paper
with printing 2 of them on the same paper.

How would i go about and do that?
Thanks.

ps. I didn't mean userform--I meant form on the other worksheet.

Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=165649)

--
Boe
------------------------------------------------------------------------
Boe's Profile: 1465
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165649

Microsoft Office Help

 

[Boe]

I am trying to create invoice for room rental
so my table will be things such as Room number,renter name, last name
rent, water, elec, internet...

and my order form is a very simple one, just need to copy data from m
table into it. but in one sheet (A4) i can print 2 room.

I was able to use the below code to print 2 connected room in 1 A
paper.
but it does not work when some of the room is empty.


.Offset(0, -1).ClearContents 'clear mark for the next time
For iCtr = LBound(myAddresses) To UBound(myAddresses)
FormWks.Range(myAddresses(iCtr)).Value _
= myCell.Offset(0, iCtr).Value

.Offset(1, -1).ClearContents
iCtr2 = LBound(myAddresses2)
FormWks.Range(myAddresses2(iCtr)).Value _
= myCell.Offset(1, iCtr).Value


[image
http://image.ohozaa.com/show.php?id=56ce46025edd96cb206fbfbb045421bc]

[image
http://image.ohozaa.com/show.php?id=d9fb640de6a0993693d8938b120c56f4]