Button (Forms Control) Problem in VBA

[Rocky0201]

Hi,

From my UserForm1 (Active Sheet is Project Definitions), when I make a selection, I would like to add a Form Control Button on my Sheet called "Start".

This part works when the selection is made in my UserForm1:

Sheets("Start").Buttons.Add(48, 196.5, 192, 19.5).Select

The Button is actually add to my Sheet called Start.

The problem I'm having is referencing the newly added Button (could be Button 1 thru Button x where x is some other number) so that I can assign a macro to the Button (Selection.OnAction = "ReturnToStart_Click"), and name the button i.e., Test.

When I ran the Record Macro this is the code produced:

Sub Macro4()
'
' Macro4 Macro
'

'
    ActiveSheet.Buttons.Add(48, 196.5, 192, 19.5).Select
    Selection.OnAction = "ReturnToStart_Click"
    ActiveSheet.Shapes("Button 7").Select
    Selection.Characters.Text = "Test"
    With Selection.Characters(Start:=1, Length:=4).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = 2
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("B14").Select
End Sub

Two issues in the above. 1) the code requires that the Sheet Start be active (I am on the Project Definitions sheet) and 2) how do I reference the newly added Button? Meaning How can I tell the Button added is Button 1, Button 2, etc.

In advance, thanks for the help.

 

[shg]

Maybe ...

Sub Macro4()
    With Worksheets("Start").Buttons.Add(48, 196.5, 192, 19.5)
        MsgBox .Name
        .OnAction = "ReturnToStart_Click"
        With .Characters
            .Text = "Test"
            With .Font
                .Name = "Calibri"
                .FontStyle = "Regular"
                .Size = 11
            End With
        End With
    End With
End Sub

 

[Rocky0201]

This is Perfect, shg!!!!

Thank you very much!!!

 

[shg]

You're welcome, Rocky.

 

[Rocky0201]

shg, is there a way to loop on my sheet called Start and, if there are Buttons previously created on Start, delete these buttons? I cannot seem to find a way to loop. I've tried For Each but I cannot find the appropriate syntax.

Thanks again...

 

[shg]

    Worksheets("Start").Buttons.Delete

 

[Rocky0201]

Again shg, I can't thank you enough. This works great!!

 

[shg]

You're welcome.