Display Lookup table from dropdown list in a form?

[EssKayKay]

Hello,
I’m back with question on displaying a text box on a form (frmPITI) based on the selection of combo box.

As Example (TryMe1.xls) –
Column E is populated with YEAR
Column F is populated with AMOUNT

The ComboBox (comBoxYEAR) has a list of years based on range(“E9:F19”).

When I select a year in the combo box (say 2025) I want the text box (txtBoxAMT) to display the value in the corresponding cell to the right. That is, cell E11 = 2025 and cell F11 = $3,333.33. So, I want the list box (txtBoxAMT) to read $3,333.33. If I select a different year (say 2027), I want the corresponding amount one column to the right of the cell that is 2027 (Year would be in E13 and the amount in F13).

Originally I was trying to do this via IF statements but was never successful (no surprise). Then I thought about using a Lookup Table. My “test” Lookup table is E9:F19. This is only as a test. I’m hoping if I get this working I will be able to apply the process to all other options.

Here is some code I have added to do some of what I have in mind:

Private Sub txtBox1_Change()
   Me.txtBox1.Value = Format(Me.txtBox1.Value, "$0,000.00")
End Sub

Private Sub UserForm_Initialize()
   txtBox1.Value = Range("F4")
   comBoxYEAR.List = Range("E9:E19").Value
End Sub

Here is a screen shot of my test file:

Thanks for viewing,
Steve K.

 

[myall_blues]

Hi Steve

You can do it with your form like this:

Private Sub comBoxYear_Change()
    '
    ' When the combobox changes, set the value in the textbox to the value in the second column of
    ' the combobox (columns are zero based, so 0=1, 1=2 etc)
    '
    txtBoxAmount.Text = Me.comBoxYear.Column(1)
End Sub
Private Sub UserForm_Initialize()
    Dim comboOptions As Variant
    '
    ' Read worksheet range (valid values) into array
    '
    comboOptions = Worksheets("Sheet1").Range("E9:F19")
    '
    ' Set combobox to have two columns, but make display width of second column zero
    '
    comBoxYear.ColumnCount = 2
    comBoxYear.ColumnWidths = "40;0"
    '
    ' Set the list in the combobox equal to the array
    '
    comBoxYear.List = comboOptions
End Sub

 

[EssKayKay]

Hi Steve

You can do it with your form like this:

Private Sub comBoxYear_Change()
    '
    ' When the combobox changes, set the value in the textbox to the value in the second column of
    ' the combobox (columns are zero based, so 0=1, 1=2 etc)
    '
    txtBoxAmount.Text = Me.comBoxYear.Column(1)
End Sub
Private Sub UserForm_Initialize()
    Dim comboOptions As Variant
    '
    ' Read worksheet range (valid values) into array
    '
    comboOptions = Worksheets("Sheet1").Range("E9:F19")
    '
    ' Set combobox to have two columns, but make display width of second column zero
    '
    comBoxYear.ColumnCount = 2
    comBoxYear.ColumnWidths = "40;0"
    '
    ' Set the list in the combobox equal to the array
    '
    comBoxYear.List = comboOptions
End Sub

Excellent - thank you very much Murray. I added a couple items and all appears to be working great. I still have much more to do but this looks very promising. Any problems, I'm sure I will be back.

Again, much appreciated. . .
Steve K.

 

[myall_blues]

You’re welcome. Thanks for the feedback.