Data from userform to worksheet

[koala]

Hello forum,

Can someone point me in the right direction with this.

I have designed a userform for data entry.

It has a combobox that is linked to a pre defined range on the worksheet (A59:A94) called "Plant_Types"

There is then 8 text boxes for the user to enter the values into (numbers). On the worksheet this data goes to the next 8 columns (B:I), rows 59:94 depending on the combobox selection. Ie. If the combobox selection is for the entry in cell A75, the data needs to go into B75:I75.

How do I then get that data to the worksheet?

Also if the combobox selection has corresponding data in the output cells already (B:I), can that data be loaded to the form so the user is able to see it when the selection is made from the combobox, and therfore hopefully not overwrite it?

This is part of a large data entry form, where the data goes to completely different areas on the worksheet, however I can modify/replicate the code to suit the other areas. The built in data entry form, whislt is ok for one section, does not have enough flexibility to cover the entire userform.

Thank you in advance for your assistance

cheers
Koala

[Jacob Hilderbrand]

You can do something like:

Sheet1.Range("A1").Value = UserForm1.TextBox1.Text
This will put the text from TextBox1 to the Range A1 on Sheet1.

[koala]

Thanks DRJ, but not quite what I am after.

The combobox is linked to cells A59:A94 and uses this range to populate the dropdown.

Whatever selection is made in the combobox, the values entered into the text boxes go to column B:I of that corresponding row.

ie if the entry listed in A65 is selected in the combobox, then values from the 8 text boxes go into B65:I65 etc.

I can get the form to place the values into columns B:I, but not the correct row.

The combobox is the critical part as the selection there points to where (which Row) the data has to go.

cheers
Koala

[Jacob Hilderbrand]

Ok. You can get the row from a ListBox/ComboBox by using the ListIndex property. The ListIndex property starts at 0, so 0 is the first entry, 1 is the second etc.


Dim Row As Long
Row = UserForm1.ComboBox1.ListIndex


Then just add the appropriate number to the variable Row to make it line up with the correct row. So if the first row you want is 59 you need to add 59 to the variable Row to get it to line up.


Dim Row As Long
Row = UserForm1.ComboBox1.ListIndex + 59


Then you can use the variable Row to make your range. Let's say we want Col A then:


Dim Row As Long
Row = UserForm1.ComboBox1.ListIndex
Range("A" & Row).Value = UserForm1.ComboBox1.Text

[koala]

Thanks DRJ

That part is now working correctly, so I can move onto the rest of the form.

I was using:


Dim i As Integer
i = CboPlantType.ListIndex + 2

thinking that it would start at the second row (first row has headers)

hence it obviously wasnt going to the right cells.

Thank you once again for your assistance, you have taught me quite a lot over the last couple of days

cheers
Koala

[Jacob Hilderbrand]

You're Welcome

Take Care

P.S. You should use Long instead of Integer, especially when dealing with rows. Also code using Long instead of Integer runs faster so there really is no reason for using Integer.