VBA Change CommandButton click event handler in runtime

Question

I have an existing CommandButton in a Form called cmd0001 and it has its event handler hardcoded in the module of the form.

Private Sub cmd0001_Click()
 'code 
End Sub

I need to "disable" that button by changing its behavior programmatically (in runtime). One possibility is to change the code of the procedure in the module using InsertLines:

Set vbComp = ThisWorkbook.VBProject.VBComponents("formName")
Set vbModule = vbComp.CodeModule
linenr = vbModule.ProcStartLine("cmd0001_Click", vbext_pk_Proc)
vbModule.InsertLines linenr + 2, "msgbox ""no"": exit sub"

Sadly this is a protected VBAProject and I get the error: "Run-time Error 50289 Can't perform operation since the project is protected".

It is possible to change the click event handler of the button? Actually I will work with many buttons and ideally I would like to have 1 global event handler for the "disabled" buttons.

All examples I found by now works with dynamically created buttons like this example but I can't find an example on how to do it with a button that already exists.

Edit 1:

Ok, I have found that is possible to create a class module and create a click handler using the following code:

'in form
Dim btnH As cCtrlHandler
Set btnH = New cButtonHandler
Set btnH.ctrl = btnTest

'class module cButtonHandler
Public WithEvents ctrl As MSForms.CommandButton
Private Sub ctrl_Click()
  MsgBox "Not authorized!"
End Sub

But the main problem is that the click handler is not overwritten! The new code, in this example the 'MsgBox "Not authorized!"' is appended to the existing code. It is possible to overwrite the event handler completely?

@RubberDuck Because the application enable/disable buttons at any moment depending the interaction of the user. That means that even If I disable a button (ex: Delete record), it will be enabled again later when someone click a listview (grid with records) for example. — Miguel Febres, Jan 05 '15 at 14:35

score 0 · Answer 1 · edited Apr 13 '17 at 12:40

You're close. Definitely on the right track. I can't take all the credit for this idea, but I think it will help you out.

First, remove the click handlers from your buttons. You can't over ride them, only add to them.

Next, create a wrapper class for MSForms.CommandButton. This class will be responsible for handling the click events uniformly across all of your buttons.

Private mClickEnabled As Boolean
Private WithEvents internalButton as MSForms.CommandButton

Public Property Let ClickEnabled(value as Boolean)
    mClickEnabled = value
End Property

Public Property Get ClickEnabled() As Boolean
    ClickEnabled = mClickEnabled
End Property

Public Sub Init(btn as MSForms.CommandButton)
    Set internalButton = btn
End Sub

Private Sub internalButton_Click()
    If ClickEnabled Then
        ' do something
    Else
        MsgBox "NotAuthorized!"
    End If
End Sub

Then in your form's intialization event, create a new collection and loop through your form's command buttons, creating new custom buttons and adding them to the collection. Calling code can set the boolean value of the custom button to determine the behavior of later click events.

Of course, this only works if you want all of your buttons to work in an absolutely identical manner. Being this is a click event, you probably don't want to do that. The best and simplest solution in this case may be just to create a private boolean for each button on your form. When an action happens where you want to disable the button, set it to false for that particular button. Then you can use a similar theory to switch the behavior.

Private cmd0001Enabled As Boolean

Private Sub cmd0001_Click()
    If cmd0001Enabled Then
        'custom action here
    Else
        HandleDisabledButtonClick
    End If
End Sub

Private Sub HandleDisabledButtonClick()
    MsgBox "Not authorized!"
End Sub

If you need to expose the booleans to external code, go ahead and create some Public Properties for them.

VBA Change CommandButton click event handler in runtime

1 Answers1