return related cell values from "search Query"

**simpo067** · 04-12-2010, 12:48 PM

Sorry Gents

I've been beaten again, the problem seems simple but obviously isn't;

I wish to enter a unique number (which has already been entered and is being used as a serial / key on a worksheet) into a text box or select it from a combobox and get returned the contents of a number of the cells within the row which the uniquet number refers to.

This seems to be exactly what I do when populating a record using VLOOKUP.

For preference I'd prefer to have this information presented in a userform to prevent my colleagues elbow typing around the worksheets.

Any guidance (even if slightly mocking as I've missed the blindingly obvious would be appreciated)

Simpo

**simpo067** · 04-13-2010, 08:56 AM

Solved the problem after a lot of flailing around.

The simplest way i found was to create a userform containing 1 combobox and a number of textboxs the combobox rowsource was linked to my array to display the unique number (or allow free type).

The following code was used to take the information from the appropriate columns in my array.

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim str1 As String, str2 As String
ComboBox1.BoundColumn = 2
TextBox1.Value = ComboBox1.Value
ComboBox1.BoundColumn = 3
TextBox2.Value = ComboBox1.Value
ComboBox1.BoundColumn = 4
TextBox3.Value = ComboBox1.Value
ComboBox1.BoundColumn = 5
TextBox4.Value = ComboBox1.Value
ComboBox1.BoundColumn = 7
TextBox5.Value = ComboBox1.Value
ComboBox1.BoundColumn = 8
TextBox6.Value = ComboBox1.Value
End Sub



Private Sub UserForm_Initialize()
'TextBox1.Value = ""
'TextBox2.Value = ""

End Sub