Change Userform Combobox Value from Combobox Column after additem has occurred

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
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.
 
Last edited:

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
Code:
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.
 
Last edited:

stapuff

Well-known Member
Joined
Feb 19, 2004
Messages
1,126
Code:
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_

Board Regular
Joined
Apr 28, 2010
Messages
167
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,815
Members
414,178
Latest member
Octavian Manoli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top