Select a TextBox on a sheet from a ComboBox

[Magic_Doctor]

Hello,

On the sheet there is a ComboBox. Next to this a TextBox.
I choose an item in the ComboBox and I would like, as soon as the item is chosen, to select the TextBox to enter data. How to do it ?
I tried well, in the ComboBox procedure: TextBox.SetFocus, TextBox.Select, TextBox.Activate ... nothing works, crash every time.
Otherwise the macro works fine.

Sub ChangeItem(ComboName$)
'When you change the item in the ComboBox list, the percentage is canceled in the TextBox and the CheckBox is unchecked if it was
'Magic_Doctor

    Dim suf As Byte, obj As Object, i As Byte, ad#
    
    suf = ExtractNumber(ComboName)                                          'ComboBox suffix
    Set obj = ActiveSheet.OLEObjects("TextBoxPP" & suf).Object              'TextBox "TextBoxPP"
    obj.Value = Format(0, "##,##0.00""%""")                                 'displays 0 + formatting (-> 0.00%)
    
    obj.Activate                                                            'DO NOT WORK!!!
    
    Set obj = ActiveSheet.OLEObjects("CheckBoxPP" & suf).Object             'CheckBox "CheckBoxPP"
    If obj Then                                                             'the CheckBox corresponding to the ComboBox is checked
        obj = 0                                                             'the CheckBox is unchecked
        CheckSolvants = CheckSolvants - 1                                   'and hop! 1 less solvent checked
        pourcents(suf) = 0                                                  'the percentage will necessarily be 0 in the dynamic array "percent ()"
        If CheckSolvants > 0 Then
            For i = 1 To NbSolvants + 1                                     '(Base 0)
                Set obj = ActiveSheet.OLEObjects("CheckBoxPP" & i).Object   'CheckBox "CheckBoxPP"
                If obj Then ad = ad + pourcents(i)                          'identification of CheckBoxes still checked and addition of their percentages
            Next
            Set obj = ActiveSheet.OLEObjects("TextBox_AddPourcent").Object  'TextBox "TextBox_AddPourcent"
            obj.Value = Format(ad, "##,##0.00""%""")                        'the TextBox "TextBox_AddPourcent" displays the sum of all percentages always checked + formatting (suffix "%")
        End If
    End If
End Sub

 

[GWteB]

You are using automation components, which VBA does not automatically recognize. VBA therefore does not know which library should be addressed. So you need to declare your variables used for automation objects more explicitly.

Sub ChangeItem(ComboName$)
'When you change the item in the ComboBox list, the percentage is canceled in the TextBox and the CheckBox is unchecked if it was
'Magic_Doctor

    Dim suf As Byte, i As Byte, ad As Double
    
    Dim tb As MSForms.TextBox
    Dim cb As MSForms.ComboBox
    
    suf = ExtractNumber(ComboName)                                          'ComboBox suffix
    Set tb = ActiveSheet.OLEObjects("TextBoxPP" & suf).Object               'TextBox "TextBoxPP"
    tb.Value = Format(0, "##,##0.00""%""")                                  'displays 0 + formatting (-> 0.00%)
    
    tb.Activate
    
    Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & suf).Object              'CheckBox "CheckBoxPP"
    If cb Then                                                              'the CheckBox corresponding to the ComboBox is checked
        cb = 0                                                              'the CheckBox is unchecked
        CheckSolvants = CheckSolvants - 1                                   'and hop! 1 less solvent checked
        pourcents(suf) = 0                                                  'the percentage will necessarily be 0 in the dynamic array "percent ()"
        If CheckSolvants > 0 Then
            For i = 1 To NbSolvants + 1                                     '(Base 0)
                Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & i).Object    'CheckBox "CheckBoxPP"
                If cb Then ad = ad + pourcents(i)                           'identification of CheckBoxes still checked and addition of their percentages
            Next
            Set tb = ActiveSheet.OLEObjects("TextBox_AddPourcent").Object   'TextBox "TextBox_AddPourcent"
            tb.Value = Format(ad, "##,##0.00""%""")                         'the TextBox "TextBox_AddPourcent" displays the sum of all percentages always checked + formatting (suffix "%")
        End If
    End If
End Sub

 

[Magic_Doctor]

Hello GWteB,

Thanks for answering me.
Unfortunately it does not work yet.
The error message:

 

[Magic_Doctor]

I made a small correction, and now it works, without really understanding why:

Sub ChangeItem(ComboName$)
'When you change the item in the ComboBox list, the percentage is canceled in the TextBox and the CheckBox is unchecked if it was
'Magic_Doctor / GWteB

    Dim suf As Byte, i As Byte, ad As Double
   
    Dim tb As MSForms.TextBox
    Dim cb As Object
   
    suf = ExtractNumber(ComboName)                                          'ComboBox suffix
    Set tb = ActiveSheet.OLEObjects("TextBoxPP" & suf).Object               'TextBox "TextBoxPP"
    tb.Value = Format(0, "##,##0.00""%""")                                  'displays 0 + formatting (-> 0.00%)
   
    tb.Activate
   
    Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & suf).Object              'CheckBox "CheckBoxPP"
    If cb Then                                                              'the CheckBox corresponding to the ComboBox is checked
        cb = 0                                                              'the CheckBox is unchecked
        CheckSolvants = CheckSolvants - 1                                   'and hop! 1 less solvent checked
        pourcents(suf) = 0                                                  'the percentage will necessarily be 0 in the dynamic array "percent ()"
        If CheckSolvants > 0 Then
            For i = 1 To NbSolvants + 1                                     '(Base 0)
                Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & i).Object    'CheckBox "CheckBoxPP"
                If cb Then ad = ad + pourcents(i)                           'identification of CheckBoxes still checked and addition of their percentages
            Next
            Set tb = ActiveSheet.OLEObjects("TextBox_AddPourcent").Object   'TextBox "TextBox_AddPourcent"
            tb.Value = Format(ad, "##,##0.00""%""")                         'the TextBox "TextBox_AddPourcent" displays the sum of all percentages always checked + formatting (suffix "%")
        End If
    End If
End Sub

The problem, as I had posed it, is thus now solved, but I did not really expect this result. What I really wanted to achieve was that the contents of the TextBox be highlighted, so that it would only suffice to enter a new percentage directly, as shown in the attached image.
Is it possible?

Anyway, thank you once again for your help. My application is progressing thanks to you.

 

[Magic_Doctor]

I just tried like this:

Sub ChangeItem(ComboName$)
'When you change the item in the ComboBox list, the percentage is canceled in the TextBox and the CheckBox is unchecked if it was
'Magic_Doctor / GWteB

    Dim suf As Byte, i As Byte, ad As Double
  
    Dim tb As MSForms.TextBox
    Dim cb As Object
  
    suf = ExtractNumber(ComboName)                                          'ComboBox suffix
    Set tb = ActiveSheet.OLEObjects("TextBoxPP" & suf).Object               'TextBox "TextBoxPP"
    tb.Value = Format(0, "##,##0.00""%""")                                  'displays 0 + formatting (-> 0.00%)
  
    tb.Activate

'    tb.SelStart = 0                                                         '
'    tb.SelLength = 4                                                        'DON'T WORK
'    tb.SetFocus                                                             '
  
    Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & suf).Object              'CheckBox "CheckBoxPP"
    If cb Then                                                              'the CheckBox corresponding to the ComboBox is checked
        cb = 0                                                              'the CheckBox is unchecked
        CheckSolvants = CheckSolvants - 1                                   'and hop! 1 less solvent checked
        pourcents(suf) = 0                                                  'the percentage will necessarily be 0 in the dynamic array "percent ()"
        If CheckSolvants > 0 Then
            For i = 1 To NbSolvants + 1                                     '(Base 0)
                Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & i).Object    'CheckBox "CheckBoxPP"
                If cb Then ad = ad + pourcents(i)                           'identification of CheckBoxes still checked and addition of their percentages
            Next
            Set tb = ActiveSheet.OLEObjects("TextBox_AddPourcent").Object   'TextBox "TextBox_AddPourcent"
            tb.Value = Format(ad, "##,##0.00""%""")                         'the TextBox "TextBox_AddPourcent" displays the sum of all percentages always checked + formatting (suffix "%")
        End If
    End If
End Sub[\Code]
But that did not work

 

[Magic_Doctor]

Now it works!

Sub ChangeItem(ComboName$)
'When you change the item in the ComboBox list, the percentage is canceled in the TextBox and the CheckBox is unchecked if it was
'Magic_Doctor / GWteB

    Dim suf As Byte, i As Byte, ad As Double
 
    Dim tb As MSForms.TextBox
    Dim cb As Object
 
    suf = ExtractNumber(ComboName)                                          'ComboBox suffix
    Set tb = ActiveSheet.OLEObjects("TextBoxPP" & suf).Object               'TextBox "TextBoxPP"
    tb.Value = Format(0, "##,##0.00""%""")                                  'displays 0 + formatting (-> 0.00%)
                                                                                                             '
    Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & suf).Object              'CheckBox "CheckBoxPP"
    If cb Then                                                              'the CheckBox corresponding to the ComboBox is checked
        cb = 0                                                              'the CheckBox is unchecked
        CheckSolvants = CheckSolvants - 1                                   'and hop! 1 less solvent checked
        pourcents(suf) = 0                                                  'the percentage will necessarily be 0 in the dynamic array "percent ()"
        If CheckSolvants > 0 Then
            For i = 1 To NbSolvants + 1                                     '(Base 0)
                Set cb = ActiveSheet.OLEObjects("CheckBoxPP" & i).Object    'CheckBox "CheckBoxPP"
                If cb Then ad = ad + pourcents(i)                           'identification of CheckBoxes still checked and addition of their percentages
            Next
            Set tb = ActiveSheet.OLEObjects("TextBox_AddPourcent").Object   'TextBox "TextBox_AddPourcent"
            tb.Value = Format(ad, "##,##0.00""%""")                         'the TextBox "TextBox_AddPourcent" displays the sum of all percentages always checked + formatting (suffix "%")
        End If
    End If

    'Highlighting TextBox content for quick entry   
    tb.Activate
    tb.SelStart = 0                                                     
    tb.SelLength = Len(tb) 
End Sub[\Code]

 

[GWteB]

I made a small correction, and now it works, without really understanding why:

Apologies. I was too focused on the title of your topic, where you mention ComboBox. However, in addition to TextBoxes, in your code you refer to CheckBoxes rather then ComboBoxes.
And since you just wanted to move the focus to a TextBox (and not a CheckBox) by invoking its Activate method (which previously failed because VBA was unable to determine if this was a valid method), you could revert to your old code, by using the generic Object type for the CheckBox's variable without issues in this case.

So this

Dim tb As MSForms.TextBox
Dim cb As MSForms.ComboBox

should look like this.

Dim tb As MSForms.TextBox
Dim cb As MSForms.CheckBox

To avoid similar problems in the future, declare your variables explicitly, even if they are ActiveX controls.

 

[Magic_Doctor]

I too should have realized it. As we say in French "it was as big as the nose in the middle of the face".
I didn't know that you could declare ActiveXs by typing them in the form "As MSForms.Object".
Thanks for your advices.

 

[GWteB]

You are welcome!
And yeah, sometimes we don't see the obvious ...