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("CommandButt on1"). _
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

Have a look at Chip's solution here. Watch out for word wrap.

http://www.google.com.hk/groups?hl=e...%40tkmsftngp13


Robin Hammond
www.enhanceddatasystems.com

"Jag Man" wrote in message
...
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("CommandButt on1"). _
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

Think you are mistaken. No event code is generated from your code. Only
the button is added. You can see how to add the code at Chip Pearson's
site:

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy


Jag Man wrote in message
...
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("CommandButt on1"). _
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


"Tom Ogilvy" wrote in message
...
Think you are mistaken. No event code is generated from your code. Only
the button is added. You can see how to add the code at Chip Pearson's
site:

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy

Thanks, Robin. That did the trick!

Ed
"Robin Hammond" wrote in message
...
Have a look at Chip's solution here. Watch out for word wrap.


http://www.google.com.hk/groups?hl=e...%40tkmsftngp13


Robin Hammond

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.

--
Regards,
Tom Ogilvy

Jag Man wrote in message
...
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


"Tom Ogilvy" wrote in message
...
Think you are mistaken. No event code is generated from your code.
Only
the button is added. You can see how to add the code at Chip Pearson's
site:

http://www.cpearson.com/excel/vbe.htm

--
Regards,
Tom Ogilvy

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

"Tom Ogilvy" wrote in message
...
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.

--

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?

Yes, it is the editor that creates it.


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

"Jag Man" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--