Hide Frame in Userform and SetFocus to ComboBox

[karenjoy511]

I am working on a Userform where the user can select a project name from a ComboBox (which is inside a frame) and if the selected project is one named "New Project" then the Userform unhides a second frame that has a TextBox where the user can enter in the name of a new project. (These two frames and their respective input boxes reside on the first page of a Multiform. The ComboBox code is in a Change event routine and the TextBox code is in an AfterUpdate event routine.)

My code works fine for each scenario I have tested except in the case when the user decides to delete the name of a new project that they previously typed into the TextBox and hits Enter to trigger the AfterUpdate event code. What I want the code to do is re-hide the second frame (with the TextBox), reset the value of the ComboBox to a null string, and re-set the focus to the ComboBox.

The only workaround I've been able to come up with is to unload the form and then re-show it, which works just fine, but logically it seems that I should be able to hide the second frame and reset the focus. Does anyone have any ideas on how to make this work?

Public EnableEvents As Boolean


Private Sub UserForm_Initialize()
    Me.EnableEvents = True
   SelectedProject = ""
    MultiPgBenefits.Value = 0
    cbobxProjectName.Value = SelectedProject
    cbobxProjectName.SetFocus
End Sub


Private Sub cbobxProjectName_Change()
    If Not Me.EnableEvents Then Exit Sub
    SelectedProject = cbobxProjectName.Value
    If SelectedProject = "" Then
        NewProjectFrame.Visible = False
    ElseIf SelectedProject = "New Project" Then 'get name of new project
        NewProjectFrame.Visible = True
        txbxProjectName.Value = ""
        txbxProjectName.SetFocus
    Else 'initialize form from a saved project
        NewProjectFrame.Visible = False
    End If
End Sub


Private Sub txbxProjectName_AfterUpdate()
    If Not Me.EnableEvents Then Exit Sub
    SelectedProject = txbxProjectName.Value
    If SelectedProject = "" Then
        Me.EnableEvents = False
        cbobxProjectName.Value = SelectedProject
        cbobxProjectName.SetFocus
        NewProjectFrame.Visible = False
        Me.EnableEvents = True
    Else
        'code to continue to the next step in the form
    End If
End Sub

Thank you.

 

[mikerickson]

Try

Private Sub txtbxProjectName_AfterUpdate()
    If txtbxProjectName = vbNullString Then
        Frame2.Visible = False
        cbobxProjectName.ListIndex = -1
        cbobxProjectName.SetFocus
    Else
        ' your code
End If

 

[karenjoy511]

Thank you, Neighbor! Setting the ListIndex to -1 works wonderfully for resetting the ComboBox.

Unfortunately I cannot find a solution to successfully set the focus back to the ComboBox other than to unload and re-load the Userform.

This seems to be a popular problem to which an answer is not forthcoming: from a TextBox, how can a programmer manually select the next control that should receive focus? I've tried various & sundry different things in the AfterUpdate, BeforeUpdate, and Exit event handlers...all to no avail (sometimes the SetFocus property will just be ignored, and sometimes it causes an error).

For the last several hours I've been playing with a simple UserForm that has a few TextBoxes and ComboBoxes (and a couple Frames), but for the life of me I cannot figure out a way to move from one TextBox to another TextBox or a ComboBox at will. For example, I can initialize the UserForm to SetFocus to any of the boxes, but from that first box I cannot make the form skip to the fourth TextBox or the third ComboBox using any of the above mentioned event handlers.

Using SetFocus in a Change event handler will allow me to skip to whatever control I want next, however, a Change event on a TextBox doesn't allow the user to enter more than one character into the box.

Any more suggestions?

 

[mikerickson]

The SetFocus should set the focus to the combo box.
But, I've noticed that sometimes when that happens, the cursor isn't visible. what happens when (after a .SetFocus instruction) you start typing?

Is there any code in the combo box's Enter event?

 

[karenjoy511]

No, there isn't any code in the ComboBox's Enter event. I've used a Change event only. Here is some test code. The test UserForm has two Frames, Frame1 with ComboBox1 and Frame2 with TextBox2. There are also a couple random TextBox's and another ComboBox on the form just for testing purposes. My goal is to show only Frame1 with ComboBox1 on the UserForm when it is initialized. Then, if the user selects "New Project" from ComboBox1, Frame2/TextBox2 will become visible so the user can enter a name for the new project. (I realize I can also use the ComboBox for this, but like having a separate entry box for the new project name.)

I was able to get my code working with one exception: if the user chooses "New Project" from ComboBox1 (which makes Frame2 and TextBox2 visible) and has entered something in TextBox2 then decides to delete that entry (leaving TextBox2 = null string) and hit Enter/Tab, then I cannot get the code to re-hide Frame2/TextBox2 and set the focus back to ComboBox1.

In the code below I've taken out the extra logic steps that evaluate the value of ComboBox1 for simplifcation. Also, the line of code Me.Frame2.Visible = False is currently commented out because it causes an error that I can't figure out how to fix.

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .AddItem "Test Item"
        .ListIndex = -1
        .SetFocus
    End With
    Me.Frame2.Visible = False
End Sub


Private Sub ComboBox1_Change()
    Me.Frame2.Visible = True
    Me.Frame2.TextBox2.SetFocus
End Sub


Private Sub TextBox2_Enter()
    If Me.TextBox2.Value = "" Then
        Me.ComboBox1.ListIndex = -1
        Me.ComboBox1.SetFocus
'        Me.Frame2.Visible = False
    End If
End Sub