Excel VBA UserForm, input data from textbox in a cell, cell is based on value in combobox-CodePudding

I am having an excel file with a bunch of UserForms, they are all working well. but I got stuck on one part.

I have a userform with a combobox1 and a textbox1.

in the combobox you can select 17,19,21,23,25,25 in the textbox a numeric value should be typed, for e.g. 80

based on the selected value in the Combobox1 (17,19,21,23,25,25 ) I want the data from the textbox (80) to be inserted in respectively column H,I,J,K,L or M of the active row. yet I cannot figure out how...

CodePudding user response：

That's rather easy:
The items in the Combobox are indexed, the first item has index 0, the second 1 and so on. You can get the selected item with the property ListIndex. If nothing is selected, it returns -1, else the index of the selected item.

Now all you need is to use this as offset to the cell in the current row and column "H". Put the following sub into the form code and call it from whatever event routine you want (button, change-event of the textbox, form close...) Just change the names TextBox1 and ComboBox1 to the names of your controls.

Sub PutValueToSheet()
    If Me.TextBox1 = "" Or Me.ComboBox1.ListIndex < 0 Then Exit Sub
    Dim cell As Range
    Set cell = ActiveSheet.Cells(ActiveCell.Row, "H").Offset(0, Me.ComboBox1.ListIndex)
    cell.Value = Me.TextBox1
End Sub