I find something that really brings a UserForm to life is, when the mouse pointer hovers over a Button, there is some indication that the Button is clickable. An example of this sort of behaviour is in Excel (or any other Office application), when the mouse pointer hovers over a clickable control in the ribbon menu then that control changes in some subtle way … a change of colour, a border around it appears or something similar. Ribbon menu controls that are not clickable (because they are disabled) do not change. Unfortunately, this 'no change' behaviour is the default for UserForm Buttons which can make users of your UserForm think the Button is not clickable.
However, it's pretty easy to add code to a UserForm to make the Buttons indicate they are clickable.
In this example, I already have a UserForm with two Buttons: one called cbOK and one called cbCancel. I've used those two in this post because it's a fairly typical set of Buttons to have on a UserForm … you will only have to make minor (and fairly obvious) changes to make this work for different numbers and/or names of Buttons.
First thing to do, in the code-behind of the UserForm, add MouseMove event handlers for the two Buttons. You've got two choices on how to do this: use the drop-downs at the top-left then top-right of the code window to add the event handler yourself or copy from below.
Within the two event handlers, add a line of code that sets the BackColor property of the Button … in this code, I'm setting it to SystemColorConstants.vbHighlight which (unlike what I said at the start of this post) is not at all a subtle change … you can select any colour you like, either another of the SystemColorConstants enum values or you can use the RGB() function or any other way you like.
You will then end up with these two Subs:
We're half way! If you run code to show the UserForm now, you'll see that the Buttons change to a colour when the mouse pointer moves over them but then the colour stays around when the mouse pointer is moved elsewhere. To fix that, first add this Sub:
This Sub loops through every control added to a UserForm, if that control is a CommandButton then it sets its BackColor property to colour SystemColorConstants.vbButtonFace (if it isn't that colour already). vbButtonFace is the default colour for a Button. If you use a different colour as the 'default' colour for your Buttons then change SystemColorConstants.vbButtonFace to that different colour.
Last step. We need ResetButtonHoverColours to be called whenever the mouse pointer is not over either of the Buttons. So, we add another MouseMove event handler but this time for the UserForm itself. You've got the same two choices as before on how to do this: use the two drop-downs at the top-left then top-right of the code window to add the event handler yourself or copy from below.
Within the event handler, we call ResetButtonHoverColours. Like this:
Now if you run code to show the UserForm, you'll see that the Buttons change to a colour when the mouse pointer moves over them and then they revert back once the mouse pointer moves away from them.
A much better user experience. Apart from my choice of colours!
A few considerations:
You need to leave a gap between the Button and the edge of the UserForm so that the UserForm_MouseMove event handler has opportunity to be triggered if the user moves the mouse pointer outside of the UserForm … even if you do this, if the user moves the mouse pointer VERY fast from the Button to outside of the UserForm then the 'highlighted' colour will remain.
If you have your Buttons within another control (eg a Frame) instead of directly within the UserForm then you will find that ResetButtonHoverColours isn't called when you move the mouse pointer away from the Button and into the Frame. But there's a simple solution: add another MouseMove event handler, this time for the Frame. From within that, again call ResetButtonHoverColours.
If you re-show the same instance of the UserForm (e.g. if you are using the default instance of the UserForm) then you may find that the Button that was used to close the UserForm is still highlighted when the UserForm is shown again. The solution is simple: add the UserForm_Activate event handler and from within it call ResetButtonHoverColours.
If you want to change UserForm Button colours on mouse hover for programmatically added Buttons, then see my post Dynamically adding controls to a UserForm and reacting to their events.