Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I assign a macro to an autoshape using VBA?
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 -- madblok ----------------------------------------------------------------------- madbloke's Profile: http://www.excelforum.com/member.php...fo&userid=1442 View this thread: http://www.excelforum.com/showthread.php?threadid=52761 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I assign a macro to an autoshape using VBA?
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 "madbloke" 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...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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I assign a macro to an autoshape using VBA?
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 "madbloke" wrote in message ... 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 "madbloke" 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...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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I assign a macro to an autoshape using VBA?
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! Don Guillett Wrote: 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 "madbloke" wrote in message ... 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 "madbloke" 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...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 ------------------------------------------------------------------------ madbloke's Profile: http://www.excelforum.com/member.php...o&userid=14422 View this thread: http://www.excelforum.com/showthread...hreadid=527616 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autoshape macro | Excel Discussion (Misc queries) | |||
Don't Print Autoshape Macro | Excel Programming | |||
Autoshape Macro | Excel Programming | |||
Remove macro from autoshape | Excel Programming | |||
AutoShape name assigned to macro | Excel Programming |