Populate textbox with value x columns to the right of combobox value

[MarkyBoy]

Hi All,

I am trying to Populate a textbox on a vb form with a value that is x number of columns to the right of combobox value, currently I have it displaying the value of the combobox...

scoresheet.TextBox1.Text = scoresheet.ComboBox1.Value

so I need it to find the combobox value on the scoresheet worksheet and use the value that is 1 column to the right of that value

once that is done I need to populate the next textbox with the value 2 columns to the right and so on, I assume this will just be a case of writing the same code with a different column number?

thanks in advance

 

[MarkyBoy]

nobody?

Cant believe that I have found something you clever people have not come across before?

 

[mikerickson]

Loading the ComboBox with the neighboring values is one approach.

Private Sub ComboBox1_Change()
    With Me.ComboBox1
        If -1 < .ListIndex Then
            Me.TextBox1.Text = .Value
            Me.TextBox2.Text = .Text
            Me.TextBox3.Text = .List(.ListIndex, 2)
        End If
    End With
End Sub

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .ColumnCount = 3
        .ColumnWidths = "100;0;0"
        .BoundColumn = 1
        .TextColumn = 2
        .List = ThisWorkbook.Sheets("sheet1").Range("a1:c25").Value
    End With
End Sub

 

[MarkyBoy]

Hi mike,

thanks for the response,

can you please guide me through this process as I can see 3 textbox values so I am a little confused as to how to change this to meet my needs

thanks

 

[MARKYB0Y]

Ok, I have amanaged to populate the text boxes based on the combo box selection using

Private Sub ComboBox1_Change()
    Dim strNamedRange As String
    Dim lRelativeRow As Long
     
    With ComboBox1
        If .ListIndex > -1 Then
            strNamedRange = .RowSource
             'ListIndex starts at zero
            lRelativeRow = .ListIndex + 1
            TextBox1 = Range(strNamedRange)(lRelativeRow, 2)
            TextBox2 = Range(strNamedRange)(lRelativeRow, 3)
            TextBox3 = Range(strNamedRange)(lRelativeRow, 4)
        End If
    End With
End Sub

this is great, however I want the user to be able to amend the values in the textbox and press an update button to save any changed values.

am I going about this the wrong way or should I be able to add some code to the update button?

thanks

 

[mikerickson]

The Initalize code is filling a multi-column list box with the values in A1:C25, hiding 2 of the columns (setting their width to 0) and setting the .TextColumn to the second one.

The ComboBox_Change event is filling
TextBox1 with the .Value of the selected list item (column A)
TextBox2 getst the .Text of the selected list item (column B)
TextBox3 gets the .List member that is on the selected row, but the third column (column C)

(ListBox.List is 0 based so, ListBox1.List(ListBox1.ListIndex,0) comes from the first column, etc)

To modify to your situation, the number of columns and the range A1:C25 should be changed in the Initialize event. In the Change event, the code for Textbox3 should be repeated to match the number of textboxes (data columns).