Select a TextBox on a sheet from a ComboBox

Magic_Doctor

Board Regular
Joined
Mar 18, 2009
Messages
55
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.
VBA Code:
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
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.

Rich (BB code):
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

Board Regular
Joined
Mar 18, 2009
Messages
55
Hello GWteB,

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

Magic_Doctor

Board Regular
Joined
Mar 18, 2009
Messages
55
I made a small correction, and now it works, without really understanding why:
VBA Code:
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?
EXEMPLE.jpg

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

Magic_Doctor

Board Regular
Joined
Mar 18, 2009
Messages
55

ADVERTISEMENT

I just tried like this:
VBA Code:
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

Board Regular
Joined
Mar 18, 2009
Messages
55
Now it works!
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Rich (BB code):
Dim tb As MSForms.TextBox
Dim cb As MSForms.ComboBox

should look like this.
Rich (BB code):
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

Board Regular
Joined
Mar 18, 2009
Messages
55
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,769
Office Version
  1. 2013
Platform
  1. Windows
You are welcome!
And yeah, sometimes we don't see the obvious ...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,678
Messages
5,838,734
Members
430,566
Latest member
ChanchalSingh

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