Can I assign a macro to an autoshape using VBA?

[madbloke]

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?

[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



--
Don Guillett
SalesAid Software
[email protected]
"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...o&userid=14422
> View this thread: http://www.excelforum.com/showthread...hreadid=527616
>

[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
[email protected]
"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...o&userid=14422
> View this thread: http://www.excelforum.com/showthread...hreadid=527616
>

[Don Guillett]

>> Should be easy to modify
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
[email protected]
"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.
>
> Don Guillett Wrote:
>> 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
>> [email protected]
>> "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...o&userid=14422
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=527616
>> >
>
>
> --
> madbloke
> ------------------------------------------------------------------------
> madbloke's Profile:
> http://www.excelforum.com/member.php...o&userid=14422
> View this thread: http://www.excelforum.com/showthread...hreadid=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!

>> Should be easy to modify
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
[email protected]
"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.
>
> Don Guillett Wrote:
>> 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
>> [email protected]
>> "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...o&userid=14422
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=527616
>> >
>
>
> --
> madbloke
> ------------------------------------------------------------------------
> madbloke's Profile:
> http://www.excelforum.com/member.php...o&userid=14422
> View this thread: http://www.excelforum.com/showthread...hreadid=527616
>