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:
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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP