Can I assign a macro to an autoshape using VBA?

[madbloke]

Basically, I've got some code which dynamically generates macros base
on the amount of autoshapes on my worksheet - what I now need to do i
get the code to assign these macros to the autoshapes - I've trie
recording a macro and then assigning the macros manually, but all thi
comes up with is

ActiveSheet.Shapes("shape").select
Selection.OnAction = "whateversub"

The autoshapes all have the same name, but a different number (I'
using a variable to go through them 1 by 1, so I've amended it t
'ActiveSheet.Shapes("shape" & a).select')

This doesn't seem to do the trick, though.

Any ideas

 

[Don Guillett]

Here is one I use to name the shapes from a list of shapes in col A and the
name in col B
Should be easy to modify

Sub NameShapes()
Sheets("checks").Select
For Each c In [setup!a4:a15]
ActiveSheet.Shapes(c).TextFrame. _
Characters.Text = c.Offset(0, 1)
Next c
End Sub

 

[madbloke]

Cheers, Don, but I'm not trying to name the shapes - I'm trying to
assign a macro to them via VBA - i.e. I want to assign macro 1 to
autoshape 1, macro 2 to autoshape 2 etc.

Here is one I use to name the shapes from a list of shapes in col A and
the
name in col B
Should be easy to modify

Sub NameShapes()
Sheets("checks").Select
For Each c In [setup!a4:a15]
ActiveSheet.Shapes(c).TextFrame. _
Characters.Text = c.Offset(0, 1)
Next c
End Sub



--
Don Guillett
SalesAid Software
(e-mail address removed)
"madbloke" <[email protected]>
wrote in
message news:[email protected]...

Basically, I've got some code which dynamically generates macros based
on the amount of autoshapes on my worksheet - what I now need to do is
get the code to assign these macros to the autoshapes - I've tried
recording a macro and then assigning the macros manually, but all this
comes up with is

ActiveSheet.Shapes("shape").select
Selection.OnAction = "whateversub"

The autoshapes all have the same name, but a different number (I'm
using a variable to go through them 1 by 1, so I've amended it to
'ActiveSheet.Shapes("shape" & a).select')

This doesn't seem to do the trick, though.

Any ideas?


--
madbloke
------------------------------------------------------------------------
madbloke's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14422
View this thread: http://www.excelforum.com/showthread.php?threadid=527616

 

[Don Guillett]

untested but should work. Try it where the name of the macro is in col D.
ActiveSheet.Shapes(c).OnAction = c.offset(,3)' "yourmacroname"


--
Don Guillett
SalesAid Software
(e-mail address removed)

Cheers, Don, but I'm not trying to name the shapes - I'm trying to
assign a macro to them via VBA - i.e. I want to assign macro 1 to
autoshape 1, macro 2 to autoshape 2 etc.

Here is one I use to name the shapes from a list of shapes in col A and
the
name in col B
Should be easy to modify

Sub NameShapes()
Sheets("checks").Select
For Each c In [setup!a4:a15]
ActiveSheet.Shapes(c).TextFrame. _
Characters.Text = c.Offset(0, 1)
Next c
End Sub



--
Don Guillett
SalesAid Software
(e-mail address removed)
"madbloke" <[email protected]>
wrote in
message news:[email protected]...

Basically, I've got some code which dynamically generates macros based
on the amount of autoshapes on my worksheet - what I now need to do is
get the code to assign these macros to the autoshapes - I've tried
recording a macro and then assigning the macros manually, but all this
comes up with is

ActiveSheet.Shapes("shape").select
Selection.OnAction = "whateversub"

The autoshapes all have the same name, but a different number (I'm
using a variable to go through them 1 by 1, so I've amended it to
'ActiveSheet.Shapes("shape" & a).select')

This doesn't seem to do the trick, though.

Any ideas?


--
madbloke
------------------------------------------------------------------------
madbloke's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14422
View this thread: http://www.excelforum.com/showthread.php?threadid=527616

 

[madbloke]

Thanks! Little bit of tweaking and that worked perfectly - I'd tried
similar and failed, so I was wondering whether the OnAction command was
right for the job. Clearly it is! Thanks again!

untested but should work. Try it where the name of the macro is in col
D.
ActiveSheet.Shapes(c).OnAction = c.offset(,3)' "yourmacroname"


--
Don Guillett
SalesAid Software
(e-mail address removed)
"madbloke" <[email protected]>
wrote in
message news:[email protected]...

Cheers, Don, but I'm not trying to name the shapes - I'm trying to
assign a macro to them via VBA - i.e. I want to assign macro 1 to
autoshape 1, macro 2 to autoshape 2 etc.

Here is one I use to name the shapes from a list of shapes in col A and
the
name in col B
Should be easy to modify

Sub NameShapes()
Sheets("checks").Select
For Each c In [setup!a4:a15]
ActiveSheet.Shapes(c).TextFrame. _
Characters.Text = c.Offset(0, 1)
Next c
End Sub



--
Don Guillett
SalesAid Software
(e-mail address removed)
"madbloke" <[email protected]>
wrote in
message
Basically, I've got some code which dynamically generates macros
based
on the amount of autoshapes on my worksheet - what I now need to do
is
get the code to assign these macros to the autoshapes - I've tried
recording a macro and then assigning the macros manually, but all
this
comes up with is

ActiveSheet.Shapes("shape").select
Selection.OnAction = "whateversub"

The autoshapes all have the same name, but a different number (I'm
using a variable to go through them 1 by 1, so I've amended it to
'ActiveSheet.Shapes("shape" & a).select')

This doesn't seem to do the trick, though.

Any ideas?


--
madbloke

------------------------------------------------------------------------
madbloke's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14422
View this thread:
http://www.excelforum.com/showthread.php?threadid=527616