Macro to select, set, and print

  • Thread starter Thread starter Randy L. Kendrick
  • Start date Start date
R

Randy L. Kendrick

I have an Excel model that is driven off cell A1 in the presentation
sheet. If you change the two-letter US State Abbreviation in A1, it
does Index Matches to populate the rest of the model with data from
other sheets related to that state. I need to create the following:

1. Display a UserForm with checkboxes to select the states to print
2. Cycle through each selected state by
a. Populating A1 on the presentation tab with the first state
selected
b. Print that page
c. Populate A1 with the next selected state
d. Print that page
e. Continue until all selected states are printed

I can create a user form with checkboxes tied to cells for TRUE or
FALSE. I do not know how to cycle through the list of TRUE values and
use the associated states to populate A1, nor do I know how to kick
off the printing and looping, nor how to end it.

Can anyone advise? I am not a guru at VBA at all, but have a fair
understanding of how some of it works. I used to be better at it, but
have not done VBA in quite a while, even though I rock at standard
Excel modelling.

Thanks in advance!
 
Randy L. Kendrick laid this down on his screen :
I have an Excel model that is driven off cell A1 in the presentation
sheet. If you change the two-letter US State Abbreviation in A1, it
does Index Matches to populate the rest of the model with data from
other sheets related to that state. I need to create the following:

1. Display a UserForm with checkboxes to select the states to print
2. Cycle through each selected state by
a. Populating A1 on the presentation tab with the first state
selected
b. Print that page
c. Populate A1 with the next selected state
d. Print that page
e. Continue until all selected states are printed

I can create a user form with checkboxes tied to cells for TRUE or
FALSE. I do not know how to cycle through the list of TRUE values and
use the associated states to populate A1, nor do I know how to kick
off the printing and looping, nor how to end it.

Can anyone advise? I am not a guru at VBA at all, but have a fair
understanding of how some of it works. I used to be better at it, but
have not done VBA in quite a while, even though I rock at standard
Excel modelling.

Thanks in advance!
Click to expand...

IMO, you'd be better off populating a listbox with the states instead
of individual checkboxes. Then just loop the list to get the states.
You could load them into an array and process the printing from there
after the userform closes. If this will work for you post back to
confirm and I'll reply with some code samples.
 
Randy L. Kendrick laid this down on his screen :














IMO, you'd be better off populating a listbox with the states instead
of individual checkboxes. Then just loop the list to get the states.
You could load them into an array and process the printing from there
after the userform closes. If this will work for you post back to
confirm and I'll reply with some code samples.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thanks for the response Garry. So, to be clear, we need to be able to
select which states to print, not print all of them every time. I
think that is what you are saying. I am not married to using
checkboxes...if I can select multiples from the listbox that would be
fine. I am rather stuck, and as I say not a VBA expert.
 
After serious thinking Randy L. Kendrick wrote :
So, to be clear, we need to be able to
select which states to print, not print all of them every time. I
think that is what you are saying. I am not married to using
checkboxes...if I can select multiples from the listbox that would be
fine.
Click to expand...

Ok! Create a userform with the following controls:

Label1: Caption=instruction to user
ListBox1
button1: Name="cmdCancel"; Caption="Cancel"
button2: Name="cmdPrint"; Caption="Print"

Since the state names are 2 letters, the listbox will be tall and
narrow so I suggest Label1 across the top; ListBox1 on the left;
buttons on the right.

Paste the following in the code window behind the userform.

Private Sub UserForm_Initialize()
Const sStates As String = "MA,GA,CA,PA" '//edit to suit
With Me.ListBox1
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
.List = Split(sStates, ",")
End With
End Sub

Sub cmdPrint_Click()
Dim n As Integer
For n = 0 To ListBox1.ListCount - 1
Range("A1") = ListBox1.List(n)
ActiveSheet.PrintOut
Next 'n
End Sub

Sub cmdCancel_Click()
Unload Me
End Sub
 
I tend to like the "keep it simple" approach so would probably just
have the states spelled out in a cell with a blank cell next to it. If
anything in the blank cell then print that state, and the next, and
the next, Could be done with a looping macro or an autofilter to show
the cells with X and then print each in the visible cells. Send your
file to dguillett1 @gmail.com if desired.
 
After serious thinking Randy L. Kendrick wrote :


Ok! Create a userform with the following controls:

  Label1: Caption=instruction to user
  ListBox1
  button1: Name="cmdCancel"; Caption="Cancel"
  button2: Name="cmdPrint"; Caption="Print"

Since the state names are 2 letters, the listbox will be tall and
narrow so I suggest Label1 across the top; ListBox1 on the left;
buttons on the right.

Paste the following in the code window behind the userform.

Private Sub UserForm_Initialize()
  Const sStates As String = "MA,GA,CA,PA" '//edit to suit
  With Me.ListBox1
    .ListStyle = fmListStyleOption
    .MultiSelect = fmMultiSelectMulti
    .List = Split(sStates, ",")
  End With
End Sub

Sub cmdPrint_Click()
  Dim n As Integer
  For n = 0 To ListBox1.ListCount - 1
    Range("A1") = ListBox1.List(n)
    ActiveSheet.PrintOut
  Next 'n
End Sub

Sub cmdCancel_Click()
  Unload Me
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Awesome. I am now at home for the evening, but will give this a try in
the morning when I get back to work. I may even putz around later this
evening on a test sheet at home. I thank both you and Don for your
guidance. I will report back tomorrow and advise to my success or
issues. You don't know how much I appreciate this.
 
Randy L. Kendrick wrote on 10/5/2011 :
Awesome. I am now at home for the evening, but will give this a try in
the morning when I get back to work. I may even putz around later this
evening on a test sheet at home. I thank both you and Don for your
guidance. I will report back tomorrow and advise to my success or
issues. You don't know how much I appreciate this.
Click to expand...

Well, I didn't get it exactly as you wanted it! I left out the part to
only print selected items, ergo...

Sub cmdPrint_Click()
Dim n As Integer
For n = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(n) Then
Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
End If 'ListBox1.Selected(n)
Next 'n
End Sub

Sorry about that!
 
Randy L. Kendrick wrote on 10/5/2011 :







Well, I didn't get it exactly as you wanted it! I left out the part to
only print selected items, ergo...

Sub cmdPrint_Click()
  Dim n As Integer
  For n = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(n) Then
      Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
    End If 'ListBox1.Selected(n)
  Next 'n
End Sub

Sorry about that!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Okay Garry, I am almost there. Great work!! Still having one issue.

I have created a button on the primary report that opens the user
form. I replaced the subroutine with your correction. The box appears
as expected. The problem is, it is not changing cell A1. It just
prints whatever state was last shown, and does so once for each state
selected. Any thoughts?

Randy
 
Randy L. Kendrick wrote on 10/5/2011 :







Well, I didn't get it exactly as you wanted it! I left out the part to
only print selected items, ergo...

Sub cmdPrint_Click()
  Dim n As Integer
  For n = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(n) Then
      Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
    End If 'ListBox1.Selected(n)
  Next 'n
End Sub

Sorry about that!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Please disregard my last post. I found an error on my part. This works
GREAT!! If we ever meet, I owe you dinner!
 
Please disregard my last post. I found an error on my part. This works
GREAT!! If we ever meet, I owe you dinner!- Hide quoted text -

- Show quoted text -
Click to expand...

Would it be a difficult thing to add an option to "Select All"?
 
Would it be a difficult thing to add an option to "Select All"?- Hide quoted text -

- Show quoted text -
Click to expand...

Hey, I figured this part out too. :) I am really learning this. :)
Not bad for an old guy.
 
Randy L. Kendrick laid this down on his screen :
Would it be a difficult thing to add an option to "Select All"?
Click to expand...

Just add a checkbox and revise the code to first check its state...

Sub cmdPrint_Click()
Dim n As Integer
For n = 0 To ListBox1.ListCount - 1
If chkSelectAll Then
Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
Else
If ListBox1.Selected(n) Then
Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
End If 'ListBox1.Selected(n)
End If 'chkSelectAll
Next 'n
End Sub
 
Randy L. Kendrick laid this down on his screen :


Just add a checkbox and revise the code to first check its state...

Sub cmdPrint_Click()
  Dim n As Integer
  For n = 0 To ListBox1.ListCount - 1
    If chkSelectAll Then
      Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
    Else
      If ListBox1.Selected(n) Then
        Range("A1") = ListBox1.List(n): ActiveSheet.PrintOut
      End If 'ListBox1.Selected(n)
    End If 'chkSelectAll
  Next 'n
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Thanks again, kind sir.
 
Back
Top