UserForm Checkboxes Output to Multiple Sheets

Hello all!

I have an excel workbook that I've put together over the years (and members of this forum have helped form to a large extent). I'm going through and updating it and once more in need of help.

In short, I have changed my UserForm inputs and have also added 10 check boxes and am needing help with the coding and making sure things go where they need to.

The main goal of the document is that I can input information into the UserForm and then the inputted information will go to Sheet Tracking Log and also to each date/month-specific sheet.

I believe I've got the coding correct for Columns Name, Policy Number, Sold Date, Policy Type, Phone Number, and Email. (If this could be checked, that would be a wonderful added bonus). However, I for the life of me, haven't been able to figure out how to code the check boxes to display in the ways in which I need them to (in appropriate columns in Tracking Log and in date/month-specific sheet). When you go into the macro coding, you'll see I've only begun to play with just the first checkbox "chbUMForm" and have gotten totally lost in just making the first one happen, let alone the other nine.

Hopefully this makes sense. If you need any clarification, please ask!

I will attach the worksheet with what I have so far. If ya'll could help at all, it would be most appreciated!


****Also, you will see a section for a drop down of LOB. This will be removed, I simply haven't done so yet.




Thank you so much!!


Ben

Your checkbox is on the UserForm it is not on the sheet.
with the code below it puts "Yes" on the sheet, however black print on a black background needs exceptionally good eyesight to see

Please Login or Register  to view this content.

Originally Posted by torachan

Your checkbox is on the UserForm it is not on the sheet.
with the code below it puts "Yes" on the sheet, however black print on a black background needs exceptionally good eyesight to see

Please Login or Register  to view this content.

I see what you're saying. My goal is that I actually want a "Yes" answer to go into the appropriate "UM Form" (if the box is checked) column in both the Tracking Log sheet and the appropriate month/date sheet. How would I make this happen then? And then apply this same coding to the other nine check boxes as well.

Rather than explain everything it will be easier for you to work through the code.
I have left it long-handed so that it is easy to follow through, there are ways of condensing the code greatly.
Two points probably best explained, your structure of saving (to the month sheet) involved reading the row position for every entry,this only needs doing once and put into a variable.
Secondly, you were reading the list into the combos at every usage, this only needs doing once at the initialize event.
Rather than hard coding the combos (with the additem) i have added a sheet(Defaults) with two tables that are read into the combos on initalisation.
Any queries I am happy to answer on this site.
torachan.

Originally Posted by torachan

Rather than explain everything it will be easier for you to work through the code.
I have left it long-handed so that it is easy to follow through, there are ways of condensing the code greatly.
Two points probably best explained, your structure of saving (to the month sheet) involved reading the row position for every entry,this only needs doing once and put into a variable.
Secondly, you were reading the list into the combos at every usage, this only needs doing once at the initialize event.
Rather than hard coding the combos (with the additem) i have added a sheet(Defaults) with two tables that are read into the combos on initalisation.
Any queries I am happy to answer on this site.
torachan.

This looks absolutely amazing! I really appreciate your help with this! I see what you were saying and looking at how you've set it up, it definitely makes way more sense. I do have two more questions: how do I change the tab-key-order of the actual userform? Basically, when I open up the input window and hit the tab key to cycle through the different boxes, etc, it jumps around all over the place and I'm wondering if there's a way where I can tell it to go in sequential order down the left side of the form first and then the right after that. And, is there a way to have the Enter key activate, or check/uncheck, the checkboxes? Hope that makes sense.

Thank you again for your help! You've been truly wonderful!


Best,


Ben

Updated file attached, I omitted to set the tab order, something normally done as point of practice.
FYI in VBA editor with form visible > top menu > View > TabOrder > fairly intuitive to use.
Have coded the check boxes to respond to enter key (they flip/flop with each alternate press of enter)
Normally the code would have been a lot shorter however all the checkboxes have individual names.
As a matter of course I would have just numbered them so I could loop through, however that would have taken as long as coding them individually.
Hope this is what you wanted.
torachan.

Originally Posted by torachan

Updated file attached, I omitted to set the tab order, something normally done as point of practice.
FYI in VBA editor with form visible > top menu > View > TabOrder > fairly intuitive to use.
Have coded the check boxes to respond to enter key (they flip/flop with each alternate press of enter)
Normally the code would have been a lot shorter however all the checkboxes have individual names.
As a matter of course I would have just numbered them so I could loop through, however that would have taken as long as coding them individually.
Hope this is what you wanted.
torachan.

This looks wonderful!! I think you've addressed all that I was needing this to do and in a far cleaner way than what I was doing. Thank you so very much for your help with this!!


Best,


Ben