Results 1 to 6 of 6

Thread: Change Userform Combobox Value from Combobox Column after additem has occurred

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Posts
    1,126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change Userform Combobox Value from Combobox Column after additem has occurred

    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

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Userform Combobox Value from Combobox Column after additem has occurred

    Quote Originally Posted by stapuff View Post
    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 by Steve_; Jul 18th, 2019 at 05:21 PM.

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Userform Combobox Value from Combobox Column after additem has occurred

    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 by Steve_; Jul 18th, 2019 at 06:13 PM.

  4. #4
    Board Regular
    Join Date
    Feb 2004
    Posts
    1,126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Userform Combobox Value from Combobox Column after additem has occurred

    Quote Originally Posted by Steve_ View Post
    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

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Userform Combobox Value from Combobox Column after additem has occurred

    Quote Originally Posted by stapuff View Post
    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.

  6. #6
    Board Regular
    Join Date
    Feb 2004
    Posts
    1,126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Userform Combobox Value from Combobox Column after additem has occurred

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •