TransWikia.com

Stop message box on a given situation Excel VBA

Stack Overflow Asked on November 17, 2021

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

My userform

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.

One Answer

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.

Answered by Eric J. on November 17, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP