Combo Box question?

[Javi]

I have a combo box with a Row Source of =(J3:J6).

J3:J6 contains the names I want the user to see and use as there selection however would like the combo box to use K3:K6 as the actual data.

I'm using this combo box on a form.

Thanks..

 

[Norie]

Exactly what modifications did you make?

How are you checking the correct value isn't being returned from the combobox?

 

[Javi]

I have the Rowsource set to =(J3:J7) and BoundColumn set to 2 as well as ColumnCount to 2.

I'm looking to select Tom and get 1 as a value.


<TABLE style="WIDTH: 265pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=354 border=0><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2275" span=2 width=64><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5745" width=162><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2275" width=64><TBODY><TR style="HEIGHT: 24pt" height=32><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 24pt; BACKGROUND-COLOR: transparent" width=64 height=32>"J"</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>"K"</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 121pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=162></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31>Tom</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 1</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31>Sidney </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 2</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31>Javier</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 3</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 4</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 5</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 6</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 23.4pt" height=31><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 23.4pt; BACKGROUND-COLOR: transparent" height=31></TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> 7</TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[Norie]

The row source should be for both column, ie J3:K7.

 

[mikerickson]

This code, in the userform's code module, will set things up. Notice that much of the code in the Initialize event could be set at design time. The optional line controls what is shown in the Dropdown.

The ComboBox_Change code shows how to use the .Value and .Text properties of the combobox to retrieve the values.

Private Sub UserForm_Initialize()
     With ComboBox1
        .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time
        
        .List = Range("J3:K6").Value
    End With
End Sub

Private Sub ComboBox1_Change()
    With ComboBox1
        If .ListIndex <> -1 Then
            MsgBox .Text & " goes with " & .Value
        End If
    End With
End Sub

Norie, you're right the .Value property of the Range was needed. (There is no substitute for testing. )

 

[Javi]

Norie that worked well Thank you.

Mikerickson I am working on placing the code in the correct place now.

 

[Javi]

Ok I'm getting closer. As you can see below I have (2) combo boxes with different ranges. If I only use one everything works well when I try to add the 2nd one it errors.

One combo box is named Who and the other is kind.

Private Sub UserForm_Initialize()
     With who
       .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time
        
        .List = Range("J3:K6").Value
                
        With kind
        
        
       .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time
        
        .List = Range("l3:M8").Value
    
       
    
    
    End With
End Sub

 

[Javi]

Got it.... I needed to add a End With
 
Thanks for all your help!!
 
Private Sub UserForm_Initialize()
     With who
       .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time
        
        .List = Range("J3:K6").Value
        
 
End With

With kind


.ColumnCount = 2: Rem can be set at design time
.ColumnWidths = ";0": Rem optional, can be set at design time
.BoundColumn = 2: Rem design time
.TextColumn = 1: Rem design time

.List = Range("l3:M8").Value




End With
End Sub

 

[Norie]

You are missing and End With.

Private Sub UserForm_Initialize()

      With who
        .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time        
        .List = Range("J3:K6").Value 
      End With               

      With kind                
        .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time     
        .List = Range("l3:M8").Value
      End With 
End Sub

 

[Javi]

This is the correct code that worked.

Private Sub UserForm_Initialize()
     With who
       .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time
        
        .List = Range("J3:K6").Value
        
        End With
        
                
        With kind
        
        
       .ColumnCount = 2: Rem can be set at design time
        .ColumnWidths = ";0": Rem optional, can be set at design time
        .BoundColumn = 2: Rem design time
        .TextColumn = 1: Rem design time
        
        .List = Range("l3:M8").Value
     
    
    
    End With
end SUB

 

[Javi]

thanks again I appreciate your help.