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).CodeModule
.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

You can assign the same macro to each of the buttons (from the Forms toolbar):

Option Explicit
sub Testme()
dim myBTN as Button
set myBtn = activesheet.buttons(application.caller)

msgbox mybtn.topleftcell & vblf & mybtn.topleftcell.row
end sub

There's lots of properties you can examine.

And make sure you put the buttons completely within the cell--else you'll be
surprised which row gets worked on.

J Streger wrote:
>
> Looks like I just have no luck with this. Constantly crashes Excel.
>
> OK So I decided to just do your original easier way with Forms buttons.
> Everything works fine and as you said, the on action copies over. Problem is
> I need to know which button was pressed. I am generating a button next to
> each line the user adds. I looked and tried to pass a parameter in the
> OnAction command, but it won't recognize "ShowOptions 12" as valid, since it
> doesn't understand 12 is a parameter (I believe Tom mentioned this
> functionality was removed in later versions of Excel.)
>
> I tried Action Button, but that refers to command menus. All I need is a way
> to determine which row the button that was pressed was on. Each button has
> the number of the row in the name so all I need is the name of the button.
>
> --
> *********************
> J Streger
> MS Office Master 2000 ed.
> MS Project White Belt 2003
>
> "Dave Peterson" wrote:
>
> > 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).CodeModule
> > .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
> >

--

Dave Peterson

I meant:

msgbox mybtn.topleftcell.address & vblf & mybtn.topleftcell.row

but .value would be another example.

Dave Peterson wrote:
>
> You can assign the same macro to each of the buttons (from the Forms toolbar):
>
> Option Explicit
> sub Testme()
> dim myBTN as Button
> set myBtn = activesheet.buttons(application.caller)
>
> msgbox mybtn.topleftcell & vblf & mybtn.topleftcell.row
> end sub
>
> There's lots of properties you can examine.
>
> And make sure you put the buttons completely within the cell--else you'll be
> surprised which row gets worked on.
>
> J Streger wrote:
> >
> > Looks like I just have no luck with this. Constantly crashes Excel.
> >
> > OK So I decided to just do your original easier way with Forms buttons.
> > Everything works fine and as you said, the on action copies over. Problem is
> > I need to know which button was pressed. I am generating a button next to
> > each line the user adds. I looked and tried to pass a parameter in the
> > OnAction command, but it won't recognize "ShowOptions 12" as valid, since it
> > doesn't understand 12 is a parameter (I believe Tom mentioned this
> > functionality was removed in later versions of Excel.)
> >
> > I tried Action Button, but that refers to command menus. All I need is a way
> > to determine which row the button that was pressed was on. Each button has
> > the number of the row in the name so all I need is the name of the button.
> >
> > --
> > *********************
> > J Streger
> > MS Office Master 2000 ed.
> > MS Project White Belt 2003
> >
> > "Dave Peterson" wrote:
> >
> > > 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).CodeModule
> > > .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
> > >
>
> --
>
> Dave Peterson

--

Dave Peterson

> When I try to dim a var as button, Intellisense shows nothing, so I assume
> there is a reference library I need to be accessing.

It's in the Excel typelib, but hidden. Use F2 to display the Object Browser,
right-click and select "Show Hidden Members". Now you'll get Intellisense
support.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"J Streger" <(E-Mail Removed)> wrote in message
news:8BB8CED8-1A8C-4CF7-B41F-(E-Mail Removed)...
> What reference library has button as a type?
>
> When I try to dim a var as button, Intellisense shows nothing, so I assume
> there is a reference library I need to be accessing.
> --
> *********************
> J Streger
> MS Office Master 2000 ed.
> MS Project White Belt 2003
>
>
>
> "Dave Peterson" wrote:
>
>> You can assign the same macro to each of the buttons (from the Forms
>> toolbar):
>>
>> Option Explicit
>> sub Testme()
>> dim myBTN as Button
>> set myBtn = activesheet.buttons(application.caller)
>>
>> msgbox mybtn.topleftcell & vblf & mybtn.topleftcell.row
>> end sub
>>
>> There's lots of properties you can examine.
>>
>> And make sure you put the buttons completely within the cell--else you'll
>> be
>> surprised which row gets worked on.
>>
>> J Streger wrote:
>> >
>> > Looks like I just have no luck with this. Constantly crashes Excel.
>> >
>> > OK So I decided to just do your original easier way with Forms buttons.
>> > Everything works fine and as you said, the on action copies over.
>> > Problem is
>> > I need to know which button was pressed. I am generating a button next
>> > to
>> > each line the user adds. I looked and tried to pass a parameter in the
>> > OnAction command, but it won't recognize "ShowOptions 12" as valid,
>> > since it
>> > doesn't understand 12 is a parameter (I believe Tom mentioned this
>> > functionality was removed in later versions of Excel.)
>> >
>> > I tried Action Button, but that refers to command menus. All I need is
>> > a way
>> > to determine which row the button that was pressed was on. Each button
>> > has
>> > the number of the row in the name so all I need is the name of the
>> > button.
>> >
>> > --
>> > *********************
>> > J Streger
>> > MS Office Master 2000 ed.
>> > MS Project White Belt 2003
>> >
>> > "Dave Peterson" wrote:
>> >
>> > > 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).CodeModule
>> > > .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
>> > >
>>
>> --
>>
>> Dave Peterson
>>

I should have added that for clarity and documentation, you should declare
it as

Dim Btn As Excel.Button

The Excel library reference is not required, but will avoid a possible
misunderstanding with MSForm's CommandButton object. Just good documentation
practice.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Chip Pearson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
>> When I try to dim a var as button, Intellisense shows nothing, so I
>> assume
>> there is a reference library I need to be accessing.
>
> It's in the Excel typelib, but hidden. Use F2 to display the Object
> Browser, right-click and select "Show Hidden Members". Now you'll get
> Intellisense support.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email address is on the web site)
>
>
>
> "J Streger" <(E-Mail Removed)> wrote in message
> news:8BB8CED8-1A8C-4CF7-B41F-(E-Mail Removed)...
>> What reference library has button as a type?
>>
>> When I try to dim a var as button, Intellisense shows nothing, so I
>> assume
>> there is a reference library I need to be accessing.
>> --
>> *********************
>> J Streger
>> MS Office Master 2000 ed.
>> MS Project White Belt 2003
>>
>>
>>
>> "Dave Peterson" wrote:
>>
>>> You can assign the same macro to each of the buttons (from the Forms
>>> toolbar):
>>>
>>> Option Explicit
>>> sub Testme()
>>> dim myBTN as Button
>>> set myBtn = activesheet.buttons(application.caller)
>>>
>>> msgbox mybtn.topleftcell & vblf & mybtn.topleftcell.row
>>> end sub
>>>
>>> There's lots of properties you can examine.
>>>
>>> And make sure you put the buttons completely within the cell--else
>>> you'll be
>>> surprised which row gets worked on.
>>>
>>> J Streger wrote:
>>> >
>>> > Looks like I just have no luck with this. Constantly crashes Excel.
>>> >
>>> > OK So I decided to just do your original easier way with Forms
>>> > buttons.
>>> > Everything works fine and as you said, the on action copies over.
>>> > Problem is
>>> > I need to know which button was pressed. I am generating a button next
>>> > to
>>> > each line the user adds. I looked and tried to pass a parameter in the
>>> > OnAction command, but it won't recognize "ShowOptions 12" as valid,
>>> > since it
>>> > doesn't understand 12 is a parameter (I believe Tom mentioned this
>>> > functionality was removed in later versions of Excel.)
>>> >
>>> > I tried Action Button, but that refers to command menus. All I need is
>>> > a way
>>> > to determine which row the button that was pressed was on. Each button
>>> > has
>>> > the number of the row in the name so all I need is the name of the
>>> > button.
>>> >
>>> > --
>>> > *********************
>>> > J Streger
>>> > MS Office Master 2000 ed.
>>> > MS Project White Belt 2003
>>> >
>>> > "Dave Peterson" wrote:
>>> >
>>> > > 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).CodeModule
>>> > > .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
>>> > >
>>>
>>> --
>>>
>>> Dave Peterson
>>>
>
>

This makes even more sense for Optionbuttons and checkboxes where those objects
share names.

Chip Pearson wrote:
>
> I should have added that for clarity and documentation, you should declare
> it as
>
> Dim Btn As Excel.Button
>
> The Excel library reference is not required, but will avoid a possible
> misunderstanding with MSForm's CommandButton object. Just good documentation
> practice.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email address is on the web site)
>
> "Chip Pearson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >
> >> When I try to dim a var as button, Intellisense shows nothing, so I
> >> assume
> >> there is a reference library I need to be accessing.
> >
> > It's in the Excel typelib, but hidden. Use F2 to display the Object
> > Browser, right-click and select "Show Hidden Members". Now you'll get
> > Intellisense support.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> > (email address is on the web site)
> >
> >
> >
> > "J Streger" <(E-Mail Removed)> wrote in message
> > news:8BB8CED8-1A8C-4CF7-B41F-(E-Mail Removed)...
> >> What reference library has button as a type?
> >>
> >> When I try to dim a var as button, Intellisense shows nothing, so I
> >> assume
> >> there is a reference library I need to be accessing.
> >> --
> >> *********************
> >> J Streger
> >> MS Office Master 2000 ed.
> >> MS Project White Belt 2003
> >>
> >>
> >>
> >> "Dave Peterson" wrote:
> >>
> >>> You can assign the same macro to each of the buttons (from the Forms
> >>> toolbar):
> >>>
> >>> Option Explicit
> >>> sub Testme()
> >>> dim myBTN as Button
> >>> set myBtn = activesheet.buttons(application.caller)
> >>>
> >>> msgbox mybtn.topleftcell & vblf & mybtn.topleftcell.row
> >>> end sub
> >>>
> >>> There's lots of properties you can examine.
> >>>
> >>> And make sure you put the buttons completely within the cell--else
> >>> you'll be
> >>> surprised which row gets worked on.
> >>>
> >>> J Streger wrote:
> >>> >
> >>> > Looks like I just have no luck with this. Constantly crashes Excel.
> >>> >
> >>> > OK So I decided to just do your original easier way with Forms
> >>> > buttons.
> >>> > Everything works fine and as you said, the on action copies over.
> >>> > Problem is
> >>> > I need to know which button was pressed. I am generating a button next
> >>> > to
> >>> > each line the user adds. I looked and tried to pass a parameter in the
> >>> > OnAction command, but it won't recognize "ShowOptions 12" as valid,
> >>> > since it
> >>> > doesn't understand 12 is a parameter (I believe Tom mentioned this
> >>> > functionality was removed in later versions of Excel.)
> >>> >
> >>> > I tried Action Button, but that refers to command menus. All I need is
> >>> > a way
> >>> > to determine which row the button that was pressed was on. Each button
> >>> > has
> >>> > the number of the row in the name so all I need is the name of the
> >>> > button.
> >>> >
> >>> > --
> >>> > *********************
> >>> > J Streger
> >>> > MS Office Master 2000 ed.
> >>> > MS Project White Belt 2003
> >>> >
> >>> > "Dave Peterson" wrote:
> >>> >
> >>> > > 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).CodeModule
> >>> > > .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
> >>> > >
> >>>
> >>> --
> >>>
> >>> Dave Peterson
> >>>
> >
> >

--

Dave Peterson