Highlighting Values in Combo Boxes for Editing

[Stevo911]

Hi Everyone

I am using Excel from the Microsoft Office (2003) package.

I have created a userform with combo boxes allowing the user to make certain selections.

Lets say that ComboBox1 has the added items: "0", "1", "2" when the userform is initialized. I require the user to select one of these values (i've tried using the 'matchrequired' property but i'm not happy with the way it handles the error of incorrectly entering data).
Lets say that the user inputs "5" instead of one of the 3 valid options. I have got this code to try and deal with the error:

Private Sub ComboBox1_AfterUpdate()

    x = Userform.ComboBox1.Value
    
    If (x = "0") Or (x = "1") Or (x = "2") Then
    'do nothing
    Else
        '*****
        [b] 
        Userform.ComboBox1.SetFocus 
        [/b]
        '*****       
        MsgBox "An invalid entry has been made. Please make sure your selection features in the drop-down menu."
        Exit Sub
    End If
End Sub

My problem occurs in between the stars ('*****). Ideally i would like to be able to highlight the existing incorrect value in ComboBox1. 2nd Prize would be to SetFocus on ComboBox1. At the moment it highlights the default value in ComboBox2.

Any help/suggestions would be greatly appreciated.
Thanks
Steve

 

[PHV]

You may try this:
Else
'*****
Userform.ComboBox[!]2[/!].SetFocus
Userform.ComboBox1.SetFocus
'*****

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stevo911]

Thanks for reply PH ...

Unfortunately i get the error: "Run Time Error '2110': Can't move focus to the control because it is invisible, not enabled or of a type that does not accept the focus."

due to the line:
Userform.ComboBox2.SetFocus

 

[PHV]

Choose a "focusable" control instead of Combobox2 ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stevo911]

If i choose a "focusable" control like a textbox i get the error:

Runtime error '-2147418113 (8000ffff)':

Unexpected call to method or property access.

 

[PHV]

Another way is to do the check in the BeforeUpdate event procedure:

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    x = Userform.ComboBox1.Value
    If x <> "0" And x <> "1" And x <> "2") Then
        Cancel = True
        MsgBox "An invalid entry has been made. Please make sure your selection features in the drop-down menu."
        Exit Sub
    End If
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Stevo911]

hey PH

Thanks again ... i'm learning quite a bit here.
Your suggestion helps things a bit as at least the following combo box (ComboBox2) is not highlighted. It seems very difficult to highlight the contents of ComboBox1 or even setfocus there.
What is also interesting is that after the 'exit sub' command the program runs the code from the line:
"x = Userform.ComboBox1.Value"
Displays the error message for the second time and then only exits the sub. Do you have any idea why it does this?

Thanks for your help so far.
Steve