Update Worksheet data from a user form

[Eurekaonide]

Dear All

I have some controls namely Text Box's , Combo Box's and List Box's on a user form and I want the user to be able to update data in these and the updates be posted back to the correct cell in the worksheet.

My First ListBox the user chooses the line (Row) in which the updates will be posted to. Therefore How do i get the other controls on the user form to relate back to this same row (different columns) ? All I seem to be able to do is get it to populate to the next empty row

 

[VoG]

Try along the lines of

Range("A" & listbox1.Value) = combobox1.Value

 

[Eurekaonide]

Do I need to declare anything to use this as it does not seem to want to work?

The code I am using to populate the data to the next empty row looks like this;

Cells(NextRow, 33) = Combobox17.Value

The NextRow Count is set as an Application Worksheet function of COUNTA

but if I replace the above with what you suggetsed it does not work

 

[VoG]

In what way does it not work?

 

[Eurekaonide]

Hi

It states

"Method 'Range' of object'_Global' failed"

Run time error '1004';

 

[VoG]

To test I created a userform with one listbox, one combobox and one command button. Code for the command button

Private Sub CommandButton1_Click()
Range("A" & ListBox1.Value).Value = ComboBox1.Value
End Sub

On selecting a row number in the listbox and a value in the combobox, clicking the command button placed the value in the correct cell.

If nothing is selected in the listbox you'll get an error.

 

[Eurekaonide]

is it becasue the Listbox value is text not the exact row number?

 

[VoG]

So what sort of values do you have in the listbox and how do they relate to row numbers?

 

[Eurekaonide]

maybe this is the issue; What is populated in the first listbox is the text in the cells of the worksheet under a named range of 'ProjectName'

What I would like is that when the user selects one of these Project names in the list box any of the other updates they maek in the user form list boxes, text boxes etc get populated in the cells within the same row as the 'ProjectName' aka ListBox1 data.

 

[VoG]

This worked for me

Private Sub CommandButton1_Click()
Dim iRow As Long
iRow = Range("ProjectName").Find(what:=ListBox1.Value, LookIn:=xlValues, lookat:=xlWhole).Row
Range("A" & iRow).Value = ComboBox1.Value
End Sub