Automatically create a button and assign VBA code.. through VBA?

[Chronix99]

Hi Guys,

On my workbook, the user is presented with several buttons to click and upon clicking one of them - it creates a new worksheet called "SummaryReport" - this worksheet will not exist in the workbook unless the user clicks that specific button.

However, once this button is clicked and the new worksheet is created - I want to insert another button on the "SummaryReport" worksheet say called "Regenerate Report" which will delete the newly created worksheet and then show another form.

The showing the initial form is easy but I want to know how I could code in VBA to create a new button on a worksheet, then assign this button some VBA code to delete that particular worksheet.

Thanks for any help!

 

[GTO]

@GTO
So do you mean create a sheet as a 'template' and add all this modifications to it then hide it. Then every time the button is clicked just create a new sheet that copies the template sheet?

Hi there,

It sounds as if your problem is solved, but to answer the question, yes. I was thinking that you were going to use an ActiveX command button, in which case maybe something like:

• Add a 'template' sheet
  • Do whatever formatting is to be done,
  • add a button,
  • in the sheet's module:
    • Change the sheet's codename to shtTemplate
    • Change the sheet's Visible property to xlVeryHidden
    • Add code:

Option Explicit
    
Private Sub CommandButton1_Click()
    Call SheetDel(Me.Index)
End Sub

In a Standard Module:

Option Explicit
    
Sub SheetDel(Index As Long)
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(Index).Delete
    Application.DisplayAlerts = True
    ThisWorkbook.Worksheets(1).Activate
End Sub

Then the code for the button to create the new sheet would be something like:

Private Sub CommandButton1_Click()
    shtTemplate.Visible = xlSheetHidden
    shtTemplate.Copy Before:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count - 1).Visible = xlSheetVisible
    shtTemplate.Visible = xlSheetVeryHidden
End Sub