How to use VBA to add a button to a sheet and assign its click event upon another button press

Question

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.

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).

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

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

End With

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):

Public Sub mainDeleteButton(sheet As Worksheet)

    Dim confirmer As New deleteConfirmationForm
    sheet.Activate
    confirmer.Show

End Sub

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

Private Sub deletePatientButton_Click()

    Call ThisWorkbook.mainDeleteButton(Me)

End Sub

Any help is more than appreciated!

You may wish to check out [](http://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data) — MikeT, Aug 08 '16 at 01:32
I had read that page, but in practice I could not get vba to register the .onAction property of the button, no matter which kind of button I used. I was curious if that had been changed, but maybe it didn't work for a different reason. — Blake Larkin, Aug 08 '16 at 01:57

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

It is possible to add the event code programmatically to the worksheet module (see this post). However, it may be easier to keep your buttons on template worksheets that already have the event code in them. Just copy your template to a new sheet, rename it, and add your patient data.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 08 '16 at 01:42

JMcD

100
7

Wow, I'm not sure why I had it ingrained in my head templates couldn't have VBA code native to them. This should make my whole project much more efficient, thanks! – Blake Larkin Aug 08 '16 at 01:55

How to use VBA to add a button to a sheet and assign its click event upon another button press

1 Answers1