Stop message box on a given situation Excel VBA

Question

I am creating a userform using Excel VBA that is meant to be used to register some sales. The form looks like this:

As you may have noticed, I am using an image as a button. This is because the CommandButton included in VBA looks very outdated. However, using an image as a button also creates me a new error (or not, depending on how you see it) that is driving me crazy. The usual process for filling this is entering a product, a quantity, a price, a customer and clicking the button to save all the information to a worksheet. The payment textbox is only filled sometimes.

I created a data validation mechanism for all these fields, including the customer combobox. If the user types an invalid entry or leaves the field empty after clicking it, a message box appears. The code for that is the following:

Private Sub cmbCustomer_AfterUpdate()

  If cmbCustomer.ListIndex > -1 Then
  
  Else
    MsgBox "Please choose a valid customer", vbExclamation
    cmbCustomer.Value = ""
  
  End If

End Sub

This works great for avoiding invalid entries. The tricky part is that, once the button is clicked, all the fields are automatically erased. The data is correctly saved, but if the last field used before clicking was cmbCustomer (or any other, actually, because all of them have a similar mechanism to avoid empty or invalid data) and the user decides to begin filling the form again starting by the product, the message box appears, because it is empty and the code detects the invalid entry. I know this is the expected behavior for my code, but this doesn't happen if I use a traditional CommandButton because when clicking it the focus goes to said button. If I use my image-based button the focus remains on the last text field used before clicking it.

One solution would be to override the message box in this specific situation (when saving the data). The second one would be to reset the focus of the form, or set focus to the image like what happens with a regular CommandButton. Any suggestions would be greatly appreciated.

Eric J. · Accepted Answer · 2020-07-22T04:25:15.173

1

You can do yourself what the traditional CommandButton does automatically: Set the focus where you want it:

Private Sub cmbCustomer_AfterUpdate()

If cmbCustomer.ListIndex > -1 Then
  
  Else
    MsgBox "Please choose a valid customer", vbExclamation
    cmbCustomer.Value = ""
    myButton.SetFocus
  End If

End Sub

If SetFocus doesn't work, be mindful of where you are in the UI event chain: Why is my .setfocus ignored?

As mentioned in the comments, an image button can't acquire the focus. A transparent CommandButton behind it can be used as a proxy.

edited Jul 22 '20 at 04:25

answered Jul 22 '20 at 01:26

Eric J.

147,927
63
340
553

That doesn't work. The alert message box keeps showing up. It's only that this time the focus is set to a different `textbox`. And the focus to the button doesn't work anyway, because you can't set focus to an image object I believe. What I want to do is to prevent that message box from ever appearing. – Fernanda Jul 22 '20 at 02:44
if VBA won't let you focus an image object, consider setting it to another UI element that will accept the focus but doesn't matter. Perhaps a hidden UI element or label? – Eric J. Jul 22 '20 at 03:21
1

I believe that'll do it. To put a transparent `CommandButton` behind my image button. That way users can even use TAB to select it. That's a great idea, thank you for your help! I would really appreciate it if you would be kind enough to add that to your reply so I can mark it as the correct one. – Fernanda Jul 22 '20 at 03:29

Stop message box on a given situation Excel VBA

1 Answers1