Creating Sub CommandButton1_Clk programmatically

  • Thread starter Thread starter Jag Man
  • Start date Start date
J

Jag Man

I am trying to create a Worksheet from within a Sub procedure. I can create
a
command button ActiveX control with
ActiveWorkbook.Sheets.Add Type:=xlWorksheet
ActiveWorkbook.ActiveSheet.Name = "mixer"
ActiveWorkbook.ActiveSheet.OLEObjects.Add
ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=140, Top:=30, _
Width:=100, Height:=40
ActiveWorkbook.ActiveSheet.OLEObjects("CommandButton1"). _
Object.Caption = "Calculate"

This automatically creates an associated Sub:

Private Sub CommandButton1_Click()

End Sub

Is there any way to then complete this "stub?" That is, I'd like to put in
its body "myFunction", programmatically,
so it becomes:


Private Sub CommandButton1_Click()
myFunct
End Sub

TIA

Ed
 
Thanks, Tom. Apparently adding the button does create the Sub header and
End Sub lines, an empty function
that is, so when I insert lines into it all I have to do is insert the body.
The like Robin pointed me to, also by
Chip, shows how this works.

I'm a real novice in VBA, as I'm sure you can tell!

Ed
 
Well, I tested it to be sure, and adding a button programmatically does not
create the sub header and end sub lines (why would it - this would be an
undesirable side effect). Perhaps you double clicked on the control after
adding it programmatically to look at the code module. This does create the
click event - but adding the control does not.
 
Hi Tom,

That's strange. I am indeed a VBA novice, so I am inclined to believe you,
but that's not what I'm
observing. If you don't mond, let me explore this a bit further with you.

Here's what I see. From the VBA editor, in a new Module in my project I
place the code:

Public Sub myNewWorksheet()
' Create the Worksheet
Dim WSName As String
WSName = "testIt"
ActiveWorkbook.Sheets.Add Type:=xlWorksheet
ActiveWorkbook.ActiveSheet.name = WSName
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ActiveWorkbook.ActiveSheet
' Create The Button
Set Btn = WS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=WS.Range("D3").Left, Top:=WS.Range("D3").Top, _
Width:=95, Height:=40)
Btn.Object.Caption = "Calculate"
Btn.name = "TheButton"
End Sub

I then switch over to Excel and hit alt-f8 to get the list of public macros.
There I see myNewWorksheet, which
I select and click on Run. The new worksheet appears, with button in place.
I do not touch the button.

I then switch back to the VBA editor. Under Microsoft Excel Objects in the
tree view I see Sheet5(testIt). I click on that,
causing a window to open in the right had side. In the selection menu at the
top left it says (General), and the
edit panel is empty. But when I select TheButton in the selection menu at
the top, there appears in the edit panel
the header for the Click event, and the End Sub. If the programmatic
creation of the button did not
create this, what did? Is it the editor?

Thanks for your patience.

Ed
 
Jag,

When you select the object in the left-side dropdown box, the
editor creates the event procedure shell for the Click event.
If the programmatic creation of the button did not
create this, what did? Is it the editor?
Click to expand...

Yes, it is the editor that creates it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Jag Man said:
Hi Tom,

That's strange. I am indeed a VBA novice, so I am inclined to believe you,
but that's not what I'm
observing. If you don't mond, let me explore this a bit further with you.

Here's what I see. From the VBA editor, in a new Module in my project I
place the code:

Public Sub myNewWorksheet()
' Create the Worksheet
Dim WSName As String
WSName = "testIt"
ActiveWorkbook.Sheets.Add Type:=xlWorksheet
ActiveWorkbook.ActiveSheet.name = WSName
Dim WS As Worksheet
Dim Btn As OLEObject
Set WS = ActiveWorkbook.ActiveSheet
' Create The Button
Set Btn =
Click to expand...
WS.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
 
Back
Top