I have the following code to copy one VBA command button to another location:

Me.cmdFilterRow19.Copy
Me.Paste Me.Cells(rngRow.Offset(-2, 0).Row,
Me.Range("FilterMatrix").Column + 3)

Me.OLEObjects("CommandButton1").Name = "cmdFilterRow" & rngRow.Offset(-2,
0).Row
---

Now I know I can access the command button as either an OLE object or an
MSforms.Commandbutton, but I can't find anyway to programmatically add
'_click' event code to the new button. Is this even possible, or am I stuck
using Excel Buttons for this situation? Thanks.



--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

Check out Chip Pearson's site:
http://cpearson.com/excel/vbe.htm

Another option (that I think is easier)...

Use a button from the Forms toolbar. The assigned macro will travel with the
copy--or you could even assign a different macro after you do the paste.



J Streger wrote:

I have the following code to copy one VBA command button to another location:

Me.cmdFilterRow19.Copy
Me.Paste Me.Cells(rngRow.Offset(-2, 0).Row,
Me.Range("FilterMatrix").Column + 3)

Me.OLEObjects("CommandButton1").Name = "cmdFilterRow" & rngRow.Offset(-2,
0).Row
---

Now I know I can access the command button as either an OLE object or an
MSforms.Commandbutton, but I can't find anyway to programmatically add
'_click' event code to the new button. Is this even possible, or am I stuck
using Excel Buttons for this situation? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

--

Dave Peterson

Yeah I know it is easier but there are graphic and interaction issues that
move me to do something a bit more complex. Thanks for th info though, looks
like just what I need.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003



"Dave Peterson" wrote:

Check out Chip Pearson's site:
http://cpearson.com/excel/vbe.htm

Another option (that I think is easier)...

Use a button from the Forms toolbar. The assigned macro will travel with the
copy--or you could even assign a different macro after you do the paste.



J Streger wrote:

I have the following code to copy one VBA command button to another location:

Me.cmdFilterRow19.Copy
Me.Paste Me.Cells(rngRow.Offset(-2, 0).Row,
Me.Range("FilterMatrix").Column + 3)

Me.OLEObjects("CommandButton1").Name = "cmdFilterRow" & rngRow.Offset(-2,
0).Row
---

Now I know I can access the command button as either an OLE object or an
MSforms.Commandbutton, but I can't find anyway to programmatically add
'_click' event code to the new button. Is this even possible, or am I stuck
using Excel Buttons for this situation? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

--

Dave Peterson

Just tried the add event and it looks like it's really not possible to add a
VBA button to a sheet and add event handler code to it in the same procedure.
This creates the module to module interaction Chip warned about. Anyone get
around this issues, as it crashes Excel every time?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003



"Dave Peterson" wrote:

Check out Chip Pearson's site:
http://cpearson.com/excel/vbe.htm

Another option (that I think is easier)...

Use a button from the Forms toolbar. The assigned macro will travel with the
copy--or you could even assign a different macro after you do the paste.



J Streger wrote:

I have the following code to copy one VBA command button to another location:

Me.cmdFilterRow19.Copy
Me.Paste Me.Cells(rngRow.Offset(-2, 0).Row,
Me.Range("FilterMatrix").Column + 3)

Me.OLEObjects("CommandButton1").Name = "cmdFilterRow" & rngRow.Offset(-2,
0).Row
---

Now I know I can access the command button as either an OLE object or an
MSforms.Commandbutton, but I can't find anyway to programmatically add
'_click' event code to the new button. Is this even possible, or am I stuck
using Excel Buttons for this situation? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

--

Dave Peterson

This worked ok for me:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("a1:b2")
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, _
Left:=.Left, Top:=.Top, _
Width:=.Width, Height:=.Height)
End With

With .Parent.VBProject.VBComponents(.CodeName).CodeModu le
.InsertLines .CreateEventProc("Click", OLEObj.Name) + 1, _
"Msgbox ""Hi"""
End With
End With

End Sub




J Streger wrote:

Just tried the add event and it looks like it's really not possible to add a
VBA button to a sheet and add event handler code to it in the same procedure.
This creates the module to module interaction Chip warned about. Anyone get
around this issues, as it crashes Excel every time?

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

"Dave Peterson" wrote:

Check out Chip Pearson's site:
http://cpearson.com/excel/vbe.htm

Another option (that I think is easier)...

Use a button from the Forms toolbar. The assigned macro will travel with the
copy--or you could even assign a different macro after you do the paste.



J Streger wrote:

I have the following code to copy one VBA command button to another location:

Me.cmdFilterRow19.Copy
Me.Paste Me.Cells(rngRow.Offset(-2, 0).Row,
Me.Range("FilterMatrix").Column + 3)

Me.OLEObjects("CommandButton1").Name = "cmdFilterRow" & rngRow.Offset(-2,
0).Row
---

Now I know I can access the command button as either an OLE object or an
MSforms.Commandbutton, but I can't find anyway to programmatically add
'_click' event code to the new button. Is this even possible, or am I stuck
using Excel Buttons for this situation? Thanks.

--
*********************
J Streger
MS Office Master 2000 ed.
MS Project White Belt 2003

--

Dave Peterson


--

Dave Peterson