Macro to select, set, and print
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! |
Macro to select, set, and print
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! 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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro to select, set, and print
On Oct 5, 4:52*pm, GS wrote:
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! 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 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. |
Macro to select, set, and print
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. 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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro to select, set, and print
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. On Oct 5, 2:20*pm, "Randy L. Kendrick" wrote: 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! |
Macro to select, set, and print
On Oct 5, 5:29*pm, GS wrote:
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. 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 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. |
Macro to select, set, and print
Randy L. Kendrick wrote on 10/5/2011 :
On Oct 5, 5:29*pm, GS wrote: 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. 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 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. 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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro to select, set, and print
On Oct 5, 6:47*pm, GS wrote:
Randy L. Kendrick wrote on 10/5/2011 : On Oct 5, 5:29*pm, GS wrote: 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. 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 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. 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 - 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 |
Macro to select, set, and print
On Oct 5, 6:47*pm, GS wrote:
Randy L. Kendrick wrote on 10/5/2011 : On Oct 5, 5:29*pm, GS wrote: 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. 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 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. 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 - Please disregard my last post. I found an error on my part. This works GREAT!! If we ever meet, I owe you dinner! |
Macro to select, set, and print
On Oct 6, 9:50*am, "Randy L. Kendrick"
wrote: On Oct 5, 6:47*pm, GS wrote: Randy L. Kendrick wrote on 10/5/2011 : On Oct 5, 5:29*pm, GS wrote: 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. 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 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. 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 - 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 - Would it be a difficult thing to add an option to "Select All"? |
Macro to select, set, and print
On Oct 6, 11:17*am, "Randy L. Kendrick"
wrote: On Oct 6, 9:50*am, "Randy L. Kendrick" wrote: On Oct 5, 6:47*pm, GS wrote: Randy L. Kendrick wrote on 10/5/2011 : On Oct 5, 5:29*pm, GS wrote: 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. 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 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. 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 - 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 - Would it be a difficult thing to add an option to "Select All"?- Hide quoted text - - Show quoted text - Hey, I figured this part out too. :) I am really learning this. :) Not bad for an old guy. |
Macro to select, set, and print
Randy L. Kendrick laid this down on his screen :
Would it be a difficult thing to add an option to "Select All"? 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 at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Macro to select, set, and print
On Oct 6, 4:23*pm, GS wrote:
Randy L. Kendrick laid this down on his screen : Would it be a difficult thing to add an option to "Select All"? 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 Thanks again, kind sir. |
All times are GMT +1. The time now is 04:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com