Solved: User Form

[emailing]

I want users to choose a name from a list of data on a form in a combo box and then have the data belonging to this name appear in the same form in text boxes so that they can modify it and then it is written back. Which VBA code can I use to connect the chosen name with the rest of the dataset in the row (without using the standard Excel user form)?

[Bob Phillips]

Is the data bound to the combobox?

[emailing]

thanks xld for the fast response! The names are in column A and I have connected the combo box with cboName.RowSource = "A2:A99" In the properties window boundcolumn is 1 Not sure if this answers your question...???? Best regards!

[Norie]

Take a look at ListIndex, since you are populating the control using RowSource that should correspond to the row the data came from.

[Bob Phillips]

thanks xld for the fast response! The names are in column A and I have connected the combo box with cboName.RowSource = "A2:A99" In the properties window boundcolumn is 1 Not sure if this answers your question...???? Best regards!

If the data is in A2:A99, then you can use


[vba]

With Worksheets("Sheet1")
Me.Textbox1.Text = .Cells(Me.Combobox1.ListIndex + 2, "B").Value
'etc.
End With
[/vba]

to retrieve it, and

[vba]

With Worksheets("Sheet1")
.Cells(Me.Combobox1.ListIndex + 2, "B").Value = Me.Textbox1.Text
'etc.
End With
[/vba]

o write it back

[unmarkedhelicopter]

cross post to JMT http://www.puremis.net/excel/cgi-bin...num=1192649090

[emailing]

Thanks Norie & xld for your responses, sorry I could not reply earlier, was very busy at my office. Your hints brought me one step further, now after choosing a name in the combo box at least something is showing in the other text boxes, but not as it is intended to do. I have added an example file. As you can see, after choosing a name, the form is always showing the same data, and not the data belonging to the name? Most probably it is a simple thing I have overlooked, but I do not have a clue. There is a lot of information in the web how to enter data via a form, but I did not find anything to load a particular data set into a form to edit data (as it is possible with the Excel standard data form, but this has its own limitations?). Any idea what I am doing wrong? Thanks a lot for your support! PS: Sorry for the crosspost in the other forum! I use these kinds of forum groups for the first time and simply did not read the rules before. Of course that was stupid. So from now on I will only use this forum. Sorry again.

[Norie]

See the attached file.

[Bob Phillips]

This is what you need for the Change form

[vba]


Private Sub cboName_Change()
With Worksheets("List")
Me.txtCode2.Text = .Cells(Me.cboName.ListIndex + 2, "B")
Me.txtAmount2.Text = .Cells(Me.cboName.ListIndex + 2, "D").Value
End With
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdOK_Click()

With Worksheets("List")
.Cells(Me.cboName.ListIndex + 2, "B").Value = Me.txtCode2.Text
.Cells(Me.cboName.ListIndex + 2, "D").Value = Me.txtAmount2.Text
End With


End Sub

Private Sub UserForm_Initialize()

cboName.RowSource = "A2:A99"

With Worksheets("List")
Me.txtCode2.Text = .Cells(Me.cboName.ListIndex + 2, "B").Value
Me.txtAmount2.Text = .Cells(Me.cboName.ListIndex + 2, "D").Value
End With

cboName.SetFocus

End Sub
[/vba]

[emailing]

Thanks to both of you, that's great, problem solved! Cheerful greetings from East-Africa!