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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top