CODEWITHSUNDEEP
vbaexcel
Blake Larkin
Blake Larkin

Reputation: 13

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

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!

Upvotes: 1

Views: 6880

Answers (1)

JMcD
JMcD

Reputation: 100

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.

Upvotes: 1

Related Questions