Make units visible when mousing into textbox or combobox

user_10134914 0
Viewed 196

enter image description hereI have a userform that requires input into various textboxes and comboboxes. The inputs are for measurements and have units associated with them. I want the userform to display which units are expected to be used when you click into each specific text/combobox.

Currently, my code works for textboxes but does not recognize comboboxes. Here is my code. 

Private Sub TextboxActions_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If InStr(1, TextboxActions.Name, "OD") > 0 Then
    UserForm1.inUnit.Visible = True
    UserForm1.mmUnit.Visible = False
    UserForm1.eaUnit.Visible = False

ElseIf InStr(1, TextboxActions.Name, "CE") > 0 Then
    UserForm1.inUnit.Visible = False
    UserForm1.mmUnit.Visible = True
    UserForm1.eaUnit.Visible = False

Else
    UserForm1.inUnit.Visible = False
    UserForm1.mmUnit.Visible = False
    UserForm1.eaUnit.Visible = True
End If
End Sub

Using this, when the user clicks a textbox it will display the units in, mm, or ea. I want this to be the case for comboboxes as well, but currently nothing happens when clicking into a combobox.

userform combobox textbox vba excel
2 Answers

First, I would use Enter event instead of Mouse up event, so same thing will happen when you Tab into the control. For each control's Enter event simply call this ShowUnit function and feed it the controls name. 

Private Sub CE2_Enter()
    Call ShowUnit(CE2)
End Sub

Private Sub OD1_Enter()
    Call ShowUnit(OD1)
End Sub

Private Sub ShowUnit(ByRef oControl As Control)
    If InStr(1, oControl.Name, "OD") > 0 Then
        UserForm1.inUnit.Visible = True
        UserForm1.mmUnit.Visible = False
        UserForm1.eaUnit.Visible = False

    ElseIf InStr(1, oControl.Name, "CE") > 0 Then
        UserForm1.inUnit.Visible = False
        UserForm1.mmUnit.Visible = True
        UserForm1.eaUnit.Visible = False

    Else
        UserForm1.inUnit.Visible = False
        UserForm1.mmUnit.Visible = False
        UserForm1.eaUnit.Visible = True
    End If
End Sub

edit: Changed names of controls to illustrate how to use the code and returned "OD" and "CE" in the instr() functions from my test values.

For additional clarification see the image below to see what's going on. The Blue is how you can use the GUI to ensure you are referencing the Controls Event. Red is indicating that we pass the full Control to the ShowUnit Function. The Purple is showing that we take are reading the Controls Name property to search for "OD" or "CE" note that these are case sensitive without using instr(1, UCASE(oControl.Name), "OD") > 0, so if your controls name is odSomeName it won't make the inUnit label visible.

enter image description here

user_99149020

Using 3 Enter() events worked for me:

Private Sub ComboBox1_Enter()

UserForm1.Label1.Visible = True
UserForm1.Label2.Visible = False
UserForm1.Label3.Visible = False

End Sub
Private Sub ComboBox2_Enter()

UserForm1.Label1.Visible = False
UserForm1.Label2.Visible = True
UserForm1.Label3.Visible = False

End Sub
Private Sub ComboBox3_Enter()

UserForm1.Label1.Visible = False
UserForm1.Label2.Visible = False
UserForm1.Label3.Visible = True

End Sub

Example of clicking in ComboBoxes (In top Userform ComboBox1 is active, in second Userform ComboBox3 is active, etc.)

img

user_76686130
Related