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!

 

[mikerickson]

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

Asside from writting the delete sheet macro, the Macro recorder will give you what you need.

The question is "which sheet is to be deleted"? If the sheet is always added at the end of the workbook, this should nail it.

Application.DisplayAlerts=False
With ThisWorkbook
    .Sheets(.Sheets.Count).Delete
End With
Application.DisplayAlerts = True

 

[Chronix99]

I guess I'm doing something wrong.. because I record the macro all I get is :

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=421.5, Top:=33.75, Width:=135, Height:= _
49.5).Select
ActiveSheet.Shapes.Range(Array("CommandButton1")).Select
Selection.Verb Verb:=xlPrimary
ActiveSheet.Shapes.Range(Array("CommandButton1")).Select

Running this doesn't create the button and it doesn't create any code anywhere to tell VBA what to do when the button is clicked.

I pressed "record macro" then created the button, changed its properties and in design mode double clicked it and wrote the code to tell it what to do.

Then I stopped recording and all it had was what I've quoted above D:

 

[mikerickson]

Are you using a button from the Forms menu, not an ActiveX command button?
P.S. It would be best if you had a macro to assign the button to before recording the creation code.
Just to get the .OnAction code in the right place.

 

[GTO]

If I am reading your first post correctly, reference the created button on the created sheet - is this button to delete the created sheet?

If so, it would seem easier to me to simply create the sheet, button, and button's code, then make the sheet veryhidden. The button on your original sheet could make a copy of the 'template' sheet.

 

[Chronix99]

@Mick

Sorry, I was using the ActiveX control. I've got this now:

ActiveSheet.Buttons.Add(421.5, 32.25, 135, 53.25).Select
Selection.OnAction = "DeleteSummaryWS"
Selection.Characters.Text = "Regenerate Report"
With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

The DeleteSummaryWS Macro is:

Sub DeleteSummaryWS()
Sheets("Menu").Select
Application.DisplayAlerts = False
Sheets("SummaryWorksheet").Delete
Application.DisplayAlerts = True
SummaryReport.Show
End Sub

The macro works a treat but when running the code above the macro code, it doesn't create a button!

@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?

 

[mikerickson]

That first bit of code worked for me.
Is it inside a Sub?

 

[Chronix99]

Weird.. yeah it is under
Private Sub CommandButton2_Click()
which creates the worksheet, has about 50 lines of formatting code, data insertion codes and then finally just before the end sub - I have this creating a button code running

 

[mikerickson]

Is ActiveSheet where you want the button?
As I said, I copy pasted that code into a blank sub, ran it and the button appeared.

 

[Chronix99]

Oh my god...

When I was recording the macro it was the activesheet, but with all the other lines of code, just before this there is a selection of another worksheet for another code to work - I totally overlooked that.


Hahah it's created about 20 buttons one on top of another in a different worksheet! Thank you! It works a treat now :D