Vlookup Textbox in Userform

[Robby19]

Hello all,

I have a combo box on a userform set to a specific range. Whenever the user selects a value from within the combobox, I need the values of 26 textboxes within the same userform to populate numbers based off of a Table (Table7) in Sheet1.

Can you provide assistance with this?

 

[Robby19]

I have started with this;

Me.txtMWHS1POP.Value = rng.Cells(1, 2).Value
        Me.txtHQMAG12POP.Value = rng.Cells(1, 3).Value
        Me.txtMALS12POP.Value = rng.Cells(1, 4).Value
        Me.txtMWSS171POP.Value = rng.Cells(1, 5).Value
        Me.txtVMFA242POP.Value = rng.Cells(1, 6).Value
        Me.txtVMGR152POP.Value = rng.Cells(1, 7).Value
        Me.txtVMFA121POP.Value = rng.Cells(1, 8).Value
        Me.txthqmag24POP.Value = rng.Cells(1, 9).Value
        Me.txtHMH463POP.Value = rng.Cells(1, 10).Value
        Me.txtHMLA367POP.Value = rng.Cells(1, 11).Value

However, I have noticed that the numbers do not calculate for the first item in the dropdown. All other selections within the dropdown seem to work fine. I have verified that the data within the table on the first selection is there. Any ideas?

 

[Norie]

Not sure what you mean, there are no calculations in that code.

By the way, is there any sort of naming convention for the textboxes? Perhaps something that corresponds to the headers in the table.

 

[Robby19]

So the first item in the dropdown is "111." However, when it is selected none of the textboxes update. The second item in the dropdown is "231." When this is selected the textboxes all update and show the results from the table. If I select "231" first and then go back and select "111" the textboxes do not update. For some reason, the textboxes do not show the results when "111" is selected. ALL other items from the dropdown work perfectly fine.

Also, as far as the naming they all follow the same pattern, txt (textbox) MWHS1 (Header 1 from table) POP (Population) - txtMWHS1POP

 

[Norie]

It works for me.

Private Sub comMOS_Change()
Dim rng As Range
Dim idx As Long

    idx = comMOS.ListIndex

    If idx = -1 Then Exit Sub

    Set rng = Sheets("Sheet1").ListObjects("MissionPop").ListRows(idx + 1).Range

    Me.txtMWHS1POP.Value = rng.Cells(1, 2).Value
    Me.txthqmag12POP.Value = rng.Cells(1, 3).Value
    Me.txtmals12POP.Value = rng.Cells(1, 4).Value
    Me.txtmwss171POP.Value = rng.Cells(1, 5).Value
    Me.txtvmfa242POP.Value = rng.Cells(1, 6).Value
    Me.txtvmgr152POP.Value = rng.Cells(1, 7).Value
    Me.txtvmfa121POP.Value = rng.Cells(1, 8).Value
    Me.txthqmag24POP.Value = rng.Cells(1, 9).Value
    Me.txtHMH463POP.Value = rng.Cells(1, 10).Value
    Me.txtHMLA367POP.Value = rng.Cells(1, 11).Value
    
End Sub

 

[Robby19]

Appreciate you posting this! This fixed the issue entirely.

 

[Robby19]

One last question. I got the VBA written to pull the remainder of the textboxes from additional tables. However, now I need to transfer new information to a different table upon click cmdbtnSave. For example;

TxtMWHS1POP = 1 (Pre-defined lookup from table MissionPop)
TxtMWHS1Adj = 3 (User inputted data, transfer to table MissionAdj)

The layout for both tables is exactly the same, to include Column A with all of the dropdown selections.

I want to be able to transfer the value the user inputted into txtMWHS1Adj to the appropriate row of MissionAdj based off of the dropdown selection (comMOS) within the userform. Any ideas?

 

[Norie]

How does column A in MissionAdj compare to column A in MissionPop?

If they are identical then you can use the same method to get the row in MissionAdj where the data should go.

To actually put the data in the table you would 'reverse' the code used to populate the textboxes, though obviously you would need to adjust the textbox names.

So something like this.

Private Sub cmdbtnSave_Click()
Dim rng As Range
Dim idx As Long

    idx = comMOS.ListIndex

    If idx = -1 Then Exit Sub

    Set rng = Sheets("Sheet1").ListObjects("MissionAdj").ListRows(idx + 1).Range

    rng.Cells(1, 2).Value  = Me.txtMWHS1ADJ.Value
    rng.Cells(1, 3).Value  = Me.txthqmag12ADJ.Value
    rng.Cells(1, 4).Value  = Me.txtmals12ADJ.Value

    ' rest of code to populate MissionAdj table
    
End Sub

 

[Robby19]

@Norie You are my hero!