Change Userform Combobox Value from Combobox Column after additem has occurred

[stapuff]

I have
ComboBox1 and CheckBox1

Private Sub UserForm_Initialize()
With Me.ComboBox1
.Clear
.ColumnCount = 3
.ColumnWidths = "50;0;0"

.AddItem "Hi"
.AddItem "Bye"
.AddItem "See Ya"

.Column(1, 0) = 1
.Column(1, 1) = 2
.Column(1, 2) = 3

.Column(2, 0) = "iH"
.Column(2, 1) = "eyB"
.Column(2, 2) = "aY eeS"
End With
End Sub


I select Hi from the list.

If I click CheckBox1=True I want Me.ComboBox1.Value = "iH" .Column 2 value

If I click CheckBox1=False I want Me.ComboBox1.Value = "Hi" .Column 0 value

The use case for this is... I will additem a list in Greek terminology. If I click the CheckBox it will show the English equivalence.


Private Sub CheckBox1_Click()
Dim a As String
a = getAlternateDescription
Me.ComboBox1.Value = a
End Sub

Private Property Get getAlternateDescription() As String
getAlternateDescription = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 2)
End Property

I get an error on line Me.ComboBox1.Value = a

Could not set the Value property. Invalid property value.

Any thoughts on how this could be done?

Thanks,

stapuff

 

[Steve_]

I have
ComboBox1 and CheckBox1

Private Sub UserForm_Initialize()
With Me.ComboBox1
.Clear
.ColumnCount = 3
.ColumnWidths = "50;0;0"

.AddItem "Hi"
.AddItem "Bye"
.AddItem "See Ya"

.Column(1, 0) = 1
.Column(1, 1) = 2
.Column(1, 2) = 3

.Column(2, 0) = "iH"
.Column(2, 1) = "eyB"
.Column(2, 2) = "aY eeS"
End With
End Sub


I select Hi from the list.

If I click CheckBox1=True I want Me.ComboBox1.Value = "iH" .Column 2 value

If I click CheckBox1=False I want Me.ComboBox1.Value = "Hi" .Column 0 value

The use case for this is... I will additem a list in Greek terminology. If I click the CheckBox it will show the English equivalence.


Private Sub CheckBox1_Click()
Dim a As String
a = getAlternateDescription
Me.ComboBox1.Value = a
End Sub

Private Property Get getAlternateDescription() As String
getAlternateDescription = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 2)
End Property

I get an error on line Me.ComboBox1.Value = a

Could not set the Value property. Invalid property value.

Any thoughts on how this could be done?

Thanks,

stapuff

Try referencing the values by using the .listindex property instead.

 

[Steve_]

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .Clear
        .ColumnCount = 3
        .ColumnWidths = "15;0;0"
        .AddItem "Hi"
        .AddItem "Bye"
        .AddItem "See Ya"
        .Column(1, 0) = 1
        .Column(1, 1) = 2
        .Column(1, 2) = 3
        .Column(2, 0) = "iH"
        .Column(2, 1) = "eyB"
        .Column(2, 2) = "aY eeS"
        .ListIndex = 0
    End With
End Sub
Private Sub CheckBox1_Click()
    If CheckBox1 Then
        Me.ComboBox1.ColumnWidths = "0;0;15"
    Else
        Me.ComboBox1.ColumnWidths = "15;0;0"
    End If
End Sub

This is likely the easiest solution for you.

 

[stapuff]

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .Clear
        .ColumnCount = 3
        .ColumnWidths = "15;0;0"
        .AddItem "Hi"
        .AddItem "Bye"
        .AddItem "See Ya"
        .Column(1, 0) = 1
        .Column(1, 1) = 2
        .Column(1, 2) = 3
        .Column(2, 0) = "iH"
        .Column(2, 1) = "eyB"
        .Column(2, 2) = "aY eeS"
        .ListIndex = 0
    End With
End Sub
Private Sub CheckBox1_Click()
    If CheckBox1 Then
        Me.ComboBox1.ColumnWidths = "0;0;15"
    Else
        Me.ComboBox1.ColumnWidths = "15;0;0"
    End If
End Sub

This is likely the easiest solution for you.

Excellent post Steve_... couldn't be more spot on.... by far the easiest solution. A direction I never thought of either.

stapuff106

 

[Steve_]

Excellent post Steve_... couldn't be more spot on.... by far the easiest solution. A direction I never thought of either.

stapuff106

Hit that like button bruh.


Also, the reason your code wasnt working was because your return function was assigning the combobox a value that resulted in a .listindex of -1.

First ive encountered that one.

 

[stapuff]

Hit them both before you responded. (Like & Thanks buttons)