I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?



"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson

I'm getting a "'1004' Application-defined or object-defined error" on the
following line:
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List

Any ideas on what might be causing the problem? Aside from this, it's
working like a charm.

"Dave Peterson" wrote:

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson

I don't have a guess.

You may want to post the code you used and a little info (anything special???)
about the listboxes?

AB wrote:

I'm getting a "'1004' Application-defined or object-defined error" on the
following line:
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List

Any ideas on what might be causing the problem? Aside from this, it's
working like a charm.

"Dave Peterson" wrote:

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

I got it to work by creating some arrays to dump the listboxes into. It's
working great.

Thank you very, very much for the advice, direction, ideas, and scripting.

-AB

"Dave Peterson" wrote:

I don't have a guess.

You may want to post the code you used and a little info (anything special???)
about the listboxes?

AB wrote:

I'm getting a "'1004' Application-defined or object-defined error" on the
following line:
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List

Any ideas on what might be causing the problem? Aside from this, it's
working like a charm.

"Dave Peterson" wrote:

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Glad you got it working.

You may want to post that snippet of code that worked for you.

AB wrote:

Dave,

I got it to work by creating some arrays to dump the listboxes into. It's
working great.

Thank you very, very much for the advice, direction, ideas, and scripting.

-AB

"Dave Peterson" wrote:

I don't have a guess.

You may want to post the code you used and a little info (anything special???)
about the listboxes?

AB wrote:

I'm getting a "'1004' Application-defined or object-defined error" on the
following line:
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List

Any ideas on what might be causing the problem? Aside from this, it's
working like a charm.

"Dave Peterson" wrote:

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

For whomever may find it helpful:

-------------------------------------------------------------------------------

Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Variant
Dim acol(1 To 3) As Variant
Dim alstBox(1 To 3) As Variant
Dim alstCont(1 To 3) As Variant
Dim aHeader(1 To 3) As Variant
Dim i As Integer
Dim lnglistCounter As Long
Set wks = Worksheets.Add

alstBox(1) = "B" & Me.lstbxOldStatus.listCount + 1
alstBox(2) = "C" & Me.lstbxOlderStatus.listCount + 1
alstBox(3) = "D" & Me.lstbxOldestStatus.listCount + 1

alstCont(1) = Me.lstbxOldStatus.List
alstCont(2) = Me.lstbxOlderStatus.List
alstCont(3) = Me.lstbxOldestStatus.List

acol(1) = "A2"
acol(2) = "B2"
acol(3) = "C2"


aHeader(1) = "3-7"
aHeader(2) = "8-14"
aHeader(3) = "15+"

i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
Cells(1, i).NumberFormat = "@"
Cells(1, i).HorizontalAlignment = xlCenter
Cells(1, i).Interior.ColorIndex = 1
Cells(1, i).Font.ColorIndex = 2
Cells(1, i).Value = aHeader(i)
Worksheets("Report").Range(acol(i),
alstBox(i)).HorizontalAlignment =
_xlRight
With ctrl
Worksheets("Report").Range(acol(i), alstBox(i)).Value =
alstCont(i)
End With
i = i + 1
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

-------------------------------------------------------------------------------

"Dave Peterson" wrote:

Glad you got it working.

You may want to post that snippet of code that worked for you.

AB wrote:

Dave,

I got it to work by creating some arrays to dump the listboxes into. It's
working great.

Thank you very, very much for the advice, direction, ideas, and scripting.

-AB

"Dave Peterson" wrote:

I don't have a guess.

You may want to post the code you used and a little info (anything special???)
about the listboxes?

AB wrote:

I'm getting a "'1004' Application-defined or object-defined error" on the
following line:
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List

Any ideas on what might be causing the problem? Aside from this, it's
working like a charm.

"Dave Peterson" wrote:

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks for posting back.

(And remember that Google now knows it, too. You can use that for your backup
version <bg.)

AB wrote:

For whomever may find it helpful:

-------------------------------------------------------------------------------

Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Variant
Dim acol(1 To 3) As Variant
Dim alstBox(1 To 3) As Variant
Dim alstCont(1 To 3) As Variant
Dim aHeader(1 To 3) As Variant
Dim i As Integer
Dim lnglistCounter As Long
Set wks = Worksheets.Add

alstBox(1) = "B" & Me.lstbxOldStatus.listCount + 1
alstBox(2) = "C" & Me.lstbxOlderStatus.listCount + 1
alstBox(3) = "D" & Me.lstbxOldestStatus.listCount + 1

alstCont(1) = Me.lstbxOldStatus.List
alstCont(2) = Me.lstbxOlderStatus.List
alstCont(3) = Me.lstbxOldestStatus.List

acol(1) = "A2"
acol(2) = "B2"
acol(3) = "C2"


aHeader(1) = "3-7"
aHeader(2) = "8-14"
aHeader(3) = "15+"

i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
Cells(1, i).NumberFormat = "@"
Cells(1, i).HorizontalAlignment = xlCenter
Cells(1, i).Interior.ColorIndex = 1
Cells(1, i).Font.ColorIndex = 2
Cells(1, i).Value = aHeader(i)
Worksheets("Report").Range(acol(i),
alstBox(i)).HorizontalAlignment =
_xlRight
With ctrl
Worksheets("Report").Range(acol(i), alstBox(i)).Value =
alstCont(i)
End With
i = i + 1
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

-------------------------------------------------------------------------------

"Dave Peterson" wrote:

Glad you got it working.

You may want to post that snippet of code that worked for you.

AB wrote:

Dave,

I got it to work by creating some arrays to dump the listboxes into. It's
working great.

Thank you very, very much for the advice, direction, ideas, and scripting.

-AB

"Dave Peterson" wrote:

I don't have a guess.

You may want to post the code you used and a little info (anything special???)
about the listboxes?

AB wrote:

I'm getting a "'1004' Application-defined or object-defined error" on the
following line:
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List

Any ideas on what might be causing the problem? Aside from this, it's
working like a charm.

"Dave Peterson" wrote:

I have no ideas what those categories are, but maybe this'll get you started:

Option Explicit
Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim wks As Worksheet
Dim oCol As Long

Set wks = Worksheets.Add

oCol = 0
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.ListBox Then
oCol = oCol + 1
With ctrl
wks.Cells(2, oCol).Resize(.Object.ListCount).Value _
= .Object.List
End With
End If
Next ctrl

Me.Hide

With wks
.UsedRange.Columns.AutoFit
.PrintOut preview:=True
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With

Unload Me

End Sub

AB wrote:

So how would I go about this? At the moment, I've got a massive
UserForm1_Initialize script that goes through and collects all the
information that needs to be on the form. I'd prefer to create a new sub to
run on the print button. I want it to create a new sheet with columns A, B,
C, and D each having a header cell with the category they represent, then
their contents below. Then, I'd like to print the worksheet and delete it
from the workbook.

I'm not sure where to start. Can someone please help get me started?

"Dave Peterson" wrote:

How about just dumping the lists to a new worksheet and printing that.



AB wrote:

To clarify, I have already tried UserForm1.PrintForm, which does a fine job
of printing the form.

However, on the form I have three list boxes with order numbers that have
been flagged for review. The printing doesn't let you see what is scrolled
down the listbox.

More than anything, I'd like to print the information dumped into the
listbox and a column header. There are other items on the form that would be
nice to have, but not critical.

"AB" wrote:

I've created a form that populates some fields with flagged items from my
spread sheet.

How do I get my print button to print the items listed on the form?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson