creating userform enteries from drop down menus

[Steve001]

Afternoon all

Sorry struggling with this one if someone can help me please

I have a userform (userform1) that uses a combo box to look at a named table on my sheet IE "Coolant Type"

I have this working fine by using

Me.ComboBox1.List = Range("Coolant").Value (stored in AW4 - AW16)

the next column over is a letter ,A,B,C,D to represent the coolant type

Above my combo box I have a label that I write a value to (stored in AX4 - AX16)

by using

UserForm1.Label4.Caption = (Coolant_type)

how do look at my drop down list the populate a string with a value one cell to the right ?

was thinking something like this

range.offset(0,1).value

I want my result to be

Oil - this would return "A" in the text box

hope I have explained my self fully

Totally confused on how to do this - Please can you put comments on still a noob


Steve

 

[Leith Ross]

Hello Steve,

Here is the coding for the UserForm. Named ranges work differently in VBA than in Excel formulas. It is a bit more verbose.

This will up[date the label once the user had made a selection from the ComboBox. The ListIndex property which starts at 0 (zero) is used as the row offset from the first cell in "Coolant". this will then point to the corresponding cell to the right 1 column over.

Dim rngCoolant  As Range


Private Sub ComboBox1_Click()
    Label4.Caption = rngCoolant.Cells(1, 1).Offset(ComboBox1.ListIndex, 1).Value
End Sub


Private Sub UserForm_Initialize()
    Set rngCoolant = ThisWorkbook.Names("Coolant").RefersToRange
    ComboBox1.List = rngCoolant.Value
End Sub

 

[Steve001]

Hi Leith Ross

Thank you I will have to have a play and see how it works so I can get my head round it

Many Thanks

Steve