ReUsing the same userforms again but populating different rows in excel????

[mjbusch1985]

Im new to this whole VBA programming stuff. Just started using VBA to make an excel spreadsheet that prompts users to input certain data. I have a spreadsheet setup that ask the users to input certain data on a userform about location and type of mechanical part we are going to be analyzing then 3 more userforms are prompted to ask 4 questions each. That all works well but what my problem is I would like to run these same 3 userforms multiple times without having to create 15x3= 45 userforms. Basically Im trying to figure out how to program excel to use the same 3 userforms but insert the data into different rows each time the userforms are called. I only know how to program the comboboxes to input data on a single cell. I would like to be able to use the same sheets over and over again until all mechanical parts have been analyzed.

Also, I need to figure out how to get some numbers to act like numbers and not variables. Each combobox within my userform have selections like 0 probable, 1 possible, 2 Unlikely. I then use IFTHEN statements to turn thos 1 Probable on the drop down into a 0,1, or 2 on the excel sheet. I would like to sum them up for each userform and don't how to get to act as a number..

I have mutliple lines of code that look like this:
If ComboBox4.Value = "0 Probable" Then Cells(22, 17).Value = "0"
If ComboBox4.Value = "1 Possible" Then Cells(22, 17).Value = "1"
If ComboBox4.Value = "2 Unlikely" Then Cells(22, 17).Value = "2"

Anyhelp with this problem would be awesome. Been trying to figure this out for a couple days and have had ZERO luck. Thanks.

[HaHoBe]

Hi, mjbusch1985,

I think there might be a better way to work around this problem. If no selection within a ComboBox has been made the ListIndex is -1. If any selection has been made this will show a ListIndex starting with 0 for the first entry (ListIndex is zero based). If your values appear in the order you placed them here you would need to check if any choice has been made and then just write the ListIndex to the cell desired:

Please Login or Register  to view this content.

For the first part of your question I think it may be better to attach a samll sample of your workbook to have a look at.

Ciao,
Holger

[mjbusch1985]

Holger,

Thanks for the help. I meant to get back on this website sooner but I went on Holiday for the past week. The first problem I had is still bugging me. I'm really not sure how to get the userforms to repeat without creating 45 sheets instead of 3. Here is my worksheet. Take a look and be don't be too scrutinizing. Im still new to VBA. It's simple. What I would like it to do is repeat the Description and 3 following userforms until the Worksheet is completely full. Any help would be greatly appreciated.

Matt

[HaHoBe]

Hi, Matt,

first suggestion from my part is that you should consider to avoid using merged cells as that would make a lot of the coding a lot easier. If you have a cell (just a single cell) and press CRTL + UP it will select the last filled cell in that column. Switch to your cell B48 and hit the combination - I would expect you to be in B13 which is a merged area from B13 to B16.

Regarding some code for EDRC (due to the merged cells there are 2 rows added instead of usually only one or use Offset to get to the next cell below):

Please Login or Register  to view this content.

IŽll try and look into the code a bit more deeply by tomorrow.

Ciao,
Holger