Macro not running through each checkbox selection
Please see attached file. If I select a location, then pick October and November, for a particular year, it only runs the code for October. I want it to repeat the code identified as Step 3, 4, and 5 for each month that was selected on the user form, regardless of what the user has selected since each time it will be different. I'm sure I need some kind of looping statement, but I don't know how to loop through the checkboxes.
Last edited by narrowgate88; 02-01-2013 at 03:29 PM.
Hi narrowgate88
Try this
Please Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
I must have put the suggested code in wrong. Will you please look at my file. I selected October and November. It did October twice instead of October and November. Also, I'd rather not have the msgbox if possible, unless that's the only way it knows that it needs to do that month. And if that's the case, then I need to tweak what pops up in the message box because that will confuse the end user.
Hi narrowgate88
I honestly can't tell what your after...Please Login or Register to view this content.
Well, let me ask this. Can I use select case to define lboMonth, Mo, and Month for each checkbox? If I could do that, then maybe I could just put your original code around my Steps 3-5. I wasn't sure how to use select case with checkboxes, which is why I've got it in the if statements.
Hi narrowgate88
Yes...you can do thisBUT lboMonth can be ONLY one thing...can't be BOTH October AND November. This is why I sayCan I use select case to define lboMonth, Mo, and MonthWhat is it you're trying to accomplish?I honestly can't tell what your after...
PS: You could write the lboMonths to an array such that if more than one checkbook is checked the array will capture all.
Last edited by jaslake; 02-01-2013 at 01:50 PM.
Is the user allowed to check more than one checkbox?
If they are you should use a listbox for the months instead of 12 separate checkboxes.
(Even if they aren't a listbox might be a good idea.)
It can be set up to alllow multiple selections and even display a checkbox for each item, if you want.
Then you can loop through the listbox checking which month has been selected.
If a month is selected then run the appropriate code, whatever that is.
By the way, you shouldn't use Year and Month as variable names, they are VBA functions.
If posting code please use code tags, see here.
Hi John,
Hopefully I can explain this easily. For each location and year, there are 13 workbooks. One workbook for each month, and then a compilation workbook. The end user needs to copy a worksheet out of the month workbook and put it into the compilation workbook. Depending on their work schedule, they may be on schedule and only need to do one month, but if they get behind, they may need to do multiple months. So I wrote the code to open the compilation workbook and then the goal is that, based on the months they select in the checkboxes, it will open that month's workbook, copy the tab into the compilation workbook, close the month workbook, and then repeat for each month they selected, in order.
Somewhere I have to define what lboMonth, Mo, and Month is for the January checkbox for example. That way, when it gets to the piece of code that tries to open the month workbook, it has the right directory path.
Does that help?
Hi Norie,
Yes, the user is allowed to select multiple checkboxes. I had thought of using a list box, but like the look of the checkboxes better. Good comment on the year and month issue. Hadn't considered that for variable naming.
John has a very good question, what are you actually trying to achieve?
If multiple months are selected what should happen?
The existing code only seems to be for one month.
PS The listbox can be set up to have a checkbox for each month.
Hi Norie,
See my response to John's question. I'm open to the idea of a listbox that shows a checkbox. Is that a setting in the listbox properties?
Ok, I figured out how to get checkboxes on a listbox.
Hi narrowgate88
You can try this...can't test itPlease Login or Register to view this content.
If you added a listbox called lstMonths here's the code to populate it.
Here's the code that'll go through the listbox checking what's been selected.Please Login or Register to view this content.
Please Login or Register to view this content.
John,
Gives me Run-time error '9': Subscript out of range on Step 4.
Norie,
Going to test your suggestion too.
Norie,
Your's works until Step 4. It cannot find the file. It's got two problems. I selected October and November. It's pulling 09 for Mo instead of 10. And it's omitting lstMonths in the directory path as well. So it's giving me
instead ofPlease Login or Register to view this content.
Truth be told, I could revise and omit the portion of the file name that says October if needed. But I need it to say 10 instead of 09 for October. So I don't know if that's an adjustment needed to your first code, or your second one from above.Please Login or Register to view this content.
What do the variables (Month & Year) look like at Step 4...are they correct?
I changed Norie's formula to this and it now pulls 10 for october instead of 09.
The lboMonth piece still wasn't working, so I changed the file naming structure to omit that piece. Once I did that, everything worked.Please Login or Register to view this content.
Attaching file and will mark completed. Thank you both John and Norie for your help today. Very much appreciated.
Oops, forgot to add the 1.
Hi narrowgate88
You're welcome...glad I could help. Thanks for the Rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks