After MsgBox, VBA ignores setfocus to ComboBox

Question

After a messageBox answer, I want the user to be directed back to the ComboBox, 'aceCount'. However, it appears to be jumping over that line entirely. In the code below, I have two message boxes "Code working" and "After focus"; both show up so I know it is reading the code in that area, it just is literally ignoring the setfocus piece. I have read every article I can and tried multiple solutions for other questions, to no avail. It also doesn't setfocus to ANYTHING on the form after the messagebox. I've tried with textboxes and comboboxes.

Edit: it appears to be setfocus on the combobox I need, but then executes to End If bolded below and runs through code. It doesn't give user the chance to resubmit.

 Private Sub SubmitButton_Click()
'Calls all necessary subs to perform required actions. If you add a sub, you     will have to call it here if you want it to perform when you submit the form.
'If you have requested any number of HA Oracle servers, checks also if ACE and F5 are empty:
    If Me.oracleCount.ListIndex > 0 Then

'If Ace and F5 are empty, MessageBox requesting review.
    If aceCount.Value = 0 And F5Count.Value = 0 Then
       If MsgBox("You have requested a HA Oracle server, but no Ace or F5. Do you need ACE or F5?", vbYesNo + vbQuestion, "ACE or F5?") = vbYes Then
'If chooses yes, goes back to form to allow user to input data
     Cancel = True
     MsgBox "code working"
     UserForm1.aceCount.SelStart = 0
     MsgBox "after focus"
'If chooses no, submits code as normal
       Else

        Call EnterData
        Call HideSheets
        Unload Me

    **End If**
'If value in either Ace or F5, submits code as normal
   ElseIf (Me.aceCount.Value <> 0 Or Me.F5Count.Value <> 0) Then
        Call EnterData
        Call HideSheets
        Unload Me

  End If
  Else
  End If
  Call EnterData
  Call HideSheets
  Unload Me

End Sub

yep, you are correct. In trying so many things, I think that line got inadvertently deleted. However, even when the code is as below, still, no focus. 'If chooses yes, goes back to form to allow user to input data Cancel = True MsgBox "code working" UserForm1.aceCount.SetFocus UserForm1.aceCount.SelStart = 0 MsgBox "after focus" 'If chooses no, submits code as normal — aoman49, Mar 24 '16 at 14:00

score 0 · Answer 1 · answered Mar 23 '16 at 13:56

0

You don't use SetFocus. The line UserForm1.aceCount.SelStart = 0 doesn't set focus to UserForm1.aceCount. If you want to set focus to that control, you should write UserForm1.aceCount.SetFocus.

If UserForm1 is a Subform of your current form, you can use

UserForm1.Form.aceCount.SetFocus
UserForm1.SetFocus

answered Mar 23 '16 at 13:56

Dorian

435
2
15

I apologize, both you and @Macro Man are correct. In editing the code so many times, that line must have been deleted. However, even when I try your solution, (as I had many times before), it doesn't work. Shows first message box prompting to make changes, shows 'code working' message box, skips setfocus, and shows 'after focus' message box. Then closes out the form. It does run the necessary code AFTER that point though, so no issues there. – aoman49 Mar 24 '16 at 14:03

After MsgBox, VBA ignores setfocus to ComboBox

1 Answers1