Search code examples
excelvbabuttonactivexisenabled

Is there a way to detect if user clicked a disabled button?

Hey, I have an UserForm into which an user inputs varius profile fields.

Once it's filled in, there is verification - if anything goes astray, the CommandButton, named save_button is disabled

What I want to achieve is: If user clicks on the button, whilst it is in the disabled state, to display a MsgBox saying he needs to correct the incorrectly filled in field

For Demonstration purposes, I'm not gonna paste here the validation procedures, so let's just pretend that the save_button.Enabled = False is set from the getgo. Produces the same result.

save_button.Enabled = False ' already ran before, pretend this executes it
Private Sub save_button_Click()
    If save_button.Enabled = False Then
        MsgBox "Clicked disabled button"
    End If
End Sub

Issue is, once a CommandButton is set to .Enabled = False then it can no longer be officially clicked (hence it can't even trigger the Click() procedure)

My next thought was to use the MouseUp as a substitute. Issue is, this triggers on any miniscule movement over the button and I don't want to bombard the user with MsgBoxes

enter image description here

Can you think of any alternatives, as to how to detect if user clicked the disabled button?

Solution

  • When a control is disabled, the click event bubbles up the tree. In your case, I guess the user form will get the click instead. If you put your save button inside a frame, that will get the click if the button is disabled. It is fairly easy to make the frame invisible, by setting

    • Caption to ""
    • BorderStyle to fmBorderStyleNone
    • SpecialEffect to fmSpecialEffectFlat

    And then size the frame so that is the same size as the button. The code is easy:

    Private Sub YourNewFrame_Click()
    MsgBox "Save button disabled!"
End Sub

    Tip: If you draw the frame, cut your button and paste it inside your new frame, it will be placed properly. Properly, as in in the right part of the hierarchy. Visually, you will have to do manually.