Calling a UserForm from a code generated button on a worksheet

[BikerDave]

I am creating a command button on a worksheet after the worksheet is completely populated. The button is supposed to call a UserForm but I keep getting errors. All of the code for this activity is located in Module 1. I tried creating the button two different ways in hopes that this would solve my problem but it didn't. The initial code I tried is commented out.

Here is the code

' ActiveSheet.Buttons.Add(400, 10, 100, 20).Select
' Selection.Characters.Text = "Run This Routine"
' With Selection.Characters(Start:=1, Length:=12).Font
' .Name = "Calibri"
' .FontStyle = "Regular"
' .Size = 11
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .Underline = xlUnderlineStyleNone
' .ColorIndex = 1
' End With
'
' Routine = ActiveSheet.Name
' ActiveSheet.Shapes.Range(Array("Button 2")).Select
' Selection.OnAction = "ShowRun"

Dim objBtn As Object
Dim ws As Worksheet

Set ws = ActiveSheet
Routine = ActiveSheet.Name
Set objBtn = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1", link:=False, _
displayasicon:=False, Left:=400, Top:=10, Width:=100, Height:=20)
objBtn.Name = "BtnRun"
ActiveSheet.OLEObjects(1).Object.Caption = "Run this routine"
Selection.OnAction = "ShowRun"

Worksheets(NewSheet).Range("h1").Select

Worksheets(NewSheet).Protect userinterfaceonly:=True

End Sub
Sub ShowRun()


frmrun.Show


End Sub


Any help is greatly appreciated.

 

[dmt32]

Does this do what you want?

 Dim btn As Object    
 Set btn = ActiveSheet.Buttons.Add(400, 10, 100, 20)


    With btn
        .Name = "BtnRun"
        .Caption = "Run this routine"
        .OnAction = "ShowRun"
    End With

Dave

 

[My Aswer Is This]

Dave:
I like your script here:
I'm always a little confused about OnAction

How can I use the same scirpt you used but have On Action Delete the Button I just created when I click on it.
My objective is to have the Button show a Caption then when I click on the Button it will Delete itself.

Does this do what you want?

 Dim btn As Object    
 Set btn = ActiveSheet.Buttons.Add(400, 10, 100, 20)


    With btn
        .Name = "BtnRun"
        .Caption = "Run this routine"
        .OnAction = "ShowRun"
    End With

Dave

 

[Fluff]

Something like

Sub ShowRun()
   Dim Btn As Object
   Set Btn = ActiveSheet.Shapes(Application.Caller)
   Btn.Delete
End Sub

 

[My Aswer Is This]

Thanks Fluff. That worked. I have always had a problem remembering how Application.caller works with OnAction

Something like

Sub ShowRun()
   Dim Btn As Object
   Set Btn = ActiveSheet.Shapes(Application.Caller)
   Btn.Delete
End Sub

 

[dmt32]

Hi,
Fluff has kindly answered your question

Dave

 

[Fluff]

Thanks Fluff. That worked. I have always had a problem remembering how Application.caller works with OnAction

You're welcome & thanks for the feedback

 

[BikerDave]

Does this do what you want?

 Dim btn As Object    
 Set btn = ActiveSheet.Buttons.Add(400, 10, 100, 20)


    With btn
        .Name = "BtnRun"
        .Caption = "Run this routine"
        .OnAction = "ShowRun"
    End With

Dave

I tried this script and I get a run-time error '9': subscript out of range.

When I go into the debugger it highlights the "frmrun.show" line from the subroutine. What is curious about this is that the form I'm trying to open is "FrmRun" with caps, but VBA isn't recognizing it.

 

[BikerDave]

Thanks again for all the help. I was able to find my mistake with the guidance of these responses.