Add macro button at end of script?

[Desu Nota from Columbus]

I have a two step process that I now have working perfectly thanks to various resources and people on this forum. Awesome.

Now I want to make it esthetically pleasing and simple for my co-workers to use.

I tried various ways of recording/writing code to add a button which when clicked runs a macro (fail).

I would like the button to be added to worksheet "Results" and fit in Column D (column width = 18) at rows 5+6 (row height combined = 25).

I am not sure if you place a button based on columns/rows or by some other means so I will add my recorded macro just in case reference is needed.

Range("D5").Select
    Application.CommandBars("Formatting").Controls.Add Type:=msoControlButton, _
        ID:=282, Before:=14
    ActiveSheet.Buttons.Add(149.25, 52.5, 89.25, 24.75).Select
    Selection.OnAction = "PERSONAL.XLS!ATest2A"
    ActiveSheet.Shapes("Button 1").Select
    Selection.Characters.Text = "Get Downtime Data"
    With Selection.Characters(Start:=1, Length:=17).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Selection.ShapeRange.ScaleWidth 1.43, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 1.45, msoFalse, msoScaleFromTopLeft
    Range("E10").Select

 

[sykes]

Why not just add the ActiveX button to your worksheet, place it where ever you want, size it, and change the properties to suit (colour / caption etc) then set the visible property to "False"?
Put your required code in the button's _click event.

Make the final line of your other code:

Me.CommandButton1.Visible = True

.....which will make the button visible to the user, then they press it - which will run your second code.

make the last line of the button's code:

Me.CommandButton1.Visible = False

.....to automatically hide the button again.

Hopefully, you get the idea - even if I've not understood your request quite correctly - i.e. don't try and programatically build the button - build it into your spreadsheet, then use code to change it's visibility properties.

 

[Desu Nota from Columbus]

I'm not quite sure exactly what you mean.

Everytime I run these macros, they will be in a new workbook. I need the macro to add the button because the sheet where I would have hidden the button doesn't exist until the macro is run.

I think to do what you are suggesting I would need to restructure my entire code to create a workbook that can be reused (which I would like to do, but I am not adept enough to do it).

 

[sykes]

Hi

There was nothing at all in your post suggesting a new workbook. The request was "........to add a button which when clicked runs a macro "

Hopefully someone else will be able to help on this.