macro to autofill template depending on box input

[vsophof]

I have a code that I have written to help fill in cells on a new sheet...
tho this code seems to be working fine...it seems to be a bit "bulky" and is kind of a pain to read thru and make corrections/changes....so i would like to condense it some...if possible

also, i would like to use either a drop down box OR option buttons to do this instead of the input box i'm using.... tho i'm not sure how to do this....any help would be great

...i tired to leave the code here but it was too long to be able to post, so i attached the file...which is probably the better thing to do so you can understand exactly what i'm trying to get done here .....the vba i'm talking about is labeled "ITMsheetAutofill"

JWO_testbook.xlsm

any other suggestions on what i have going on here in this book would be great

[davesexcel]

Hi,
What is it you are trying to accomplish?

[vsophof]

With the autofill macro, I would like it to bring up a box where the user can select what inspection frequency then choose equipment type and it autofills certain cells accordingly.

[davesexcel]

With the autofill macro, I would like it to bring up a box where the user can select what inspection frequency then choose equipment type and it autofills certain cells accordingly.

Yes,
Your attachment has over 25 modules most of them being empty, and all your sheets tabs are hidden.
Please supply a sample workbook that we can all understand.
It needs to show your original layout and what your desired outcome should be.

[vsophof]

Ok, I will when I get back to the house.
Thanks

[vsophof]

here is the file...i didn't realize i had so many empty modules...so i deleted all the emptys....module 24 has the macro i did that i would like done differently...it can also be run by ctrl-l....
i made all the tabs visible, and the tab " " is the template i use... (yes i used (space) as a tab name).... the tabs labeled oc-##### are examples of what kinds of stuff i want to be autofilled.... equipment type, inspection freq, etc...

i'm trying to create this book for a guy who isn't very computer literate at all...i would just like him to be able to click on the information instead of having to type in a number...even a drop down box would be great....currently its set up on two boxes popping up back to back...if i could get it as one where you can make two selections in one box...that would be better...

hopes that makes sense....
JWO_test_book.xlsm

[davesexcel]

I can imagine the hours and hours you have spent on this.
Another way to comunicate with the user is to use a userform, that way you would not have to be limited to just one question like an input box.
I have attached a very simple userform example.
This link will show you how to build a userform for a textbox
http://www.davesexcel.com/createauserform.htm

You can google Excel Userforms and will get lots of hits.

Removing selects in code helps run the code much faster as well

Please Login or Register  to view this content.

Can be coverted to.

Please Login or Register  to view this content.

I believe you sill have to have cells selected though when adding borders.

Another thought is to use a Sheet Template and copy it for your next job form.
the code....

Please Login or Register  to view this content.

Only two lines of code, you could have one for each catagory and have them hidden.

[vsophof]

I can make that work nicely! The only thing i would like to change is to make combobox2 to display its contents based on the value selected in combobox1...
how would i go about that?

[davesexcel]

Check this out, the code should work for a userform
http://www.davesexcel.com/dependentcombobox.htm

[vsophof]

TYVM! that worked out great