How to use VBA to add a button to a sheet and assign its click event upon another button press
<p>I am trying to automate a quarterly patient report for a local pharmacy and in doing so I have transferred it to excel. One portion of the automation is an add patient button on the cover page of the report that goes to a form for relevent information. The ok button on the form takes the information and formats it in a new sheet named according to the patient's name. The button also adds two newly created buttons to the patient sheet, a delete and edit button. I can create the buttons and place them, but I can not find any way to assign a click event to the buttons, since they are considered new objects on each page.</p>

<p>I have moved the button's main code to the workbook itself, so all I really need to put in the button's click event is a call to that method, but I can't find any way to access the new buttons' click events through vba, and since I need to call a method in VBA itself, I'm not sure I can use a macro either (fair note, I am not all that familiar with excel macros, so if the solution lies in them, I can use that too).</p>

<p>Here is the code that instantiates and places/sizes the delete button on the new sheet:</p>

<pre><code>Dim btn As OLEObject
Set btn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False)
With btn
    .Name = "deletePatientButton"
    .Object.Caption = "Delete Patient"
    .Top = 5.25
    .Left = 290.25
    .Width = 75
    .Height = 24.75
    .PrintObject = False
</code></pre>

<p>End With</p>

<p>Here is the main method of the delete button placed in the workbook code itself (note it only really calls another verification form, so this may be redundant, but I wanted to put it in the workbook section for testing since I assumed it would have the largest scope):</p>

<pre><code>Public Sub mainDeleteButton(sheet As Worksheet)

    Dim confirmer As New deleteConfirmationForm
    sheet.Activate
    confirmer.Show

End Sub
</code></pre>

<p>Finally, here is an example of the click event I am hoping to be able to place, or replace with another solution:</p>

<pre><code>Private Sub deletePatientButton_Click()

    Call ThisWorkbook.mainDeleteButton(Me)

End Sub
</code></pre>

<p>Any help is more than appreciated!</p>