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?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks