none
Assign Macros - To Worksheet Shapes Automatically RRS feed

  • Question

  • Hi all,

    I am trying to assign macros to shapes in my worksheet this is what i have

    Column 4                 Column 5                  
    Macro Name1            ShapeNameA        
    MacroName2             ShapeRandom

    I would like to assign all the macros to the shapes.

    I have create a loop below

    Sub SetMacroButton()
    
    
    Dim ws                  As Worksheet
    Dim i                      As Integer
    Dim oShape         As Shape
    
    
        Set ws = Worksheets("Macro")
        
       
        For i = 3 To ws.Cells(ws.Rows.Count, 4).End(xlUp).Row Step 1
     
        ws.oShape.Range(ws.Cells(i, 5).Value).Select
        
        Selection.OnAction = ws.Cells(i, 4).Value
        
                     
        Next i
    
    End Sub



    please can you advise what the problem is, does this make sense?

    Thank you

    Sam

    • Edited by Sam_N8 Saturday, June 25, 2016 7:59 PM To Clarify Details
    Saturday, June 25, 2016 1:42 PM

Answers

  • Sub SetShapesMacros()
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim iShape As Shape
        
        Set ws = Worksheets("Macro")
        For i = 3 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
            Set iShape = ws.Shapes(ws.Cells(i, "E").Value2)
            iShape.OnAction = ws.Cells(i, "D").Value2
        Next i
    End Sub
    
    


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Sam_N8 Saturday, June 25, 2016 9:10 PM
    Saturday, June 25, 2016 8:17 PM

All replies

  • When you say the shape is in column 6 I don't understand.  As far as I know shapes float above the grid.  They aren't attached to a cell.  You can align it to a cell.  You could search through all the shapes and find the one closet to the cell.  Maybe you could provide an image of what you want.
    Saturday, June 25, 2016 6:30 PM
  • Hi there,

    thanks for your response, I simply mean I put the shape in the cell

    Its not attached to the cell  - I just put it there

    So I would loop through the shapes and apply the macro name in col 4 to the shape name in col5

    Column4               Column 5

    Macro1   -------  >  Shape1

    Macro2     -----  ShapeRectangle

    Macro3  -----  Shape3

    etc

    thanks

    Sam

    Saturday, June 25, 2016 7:23 PM
  • Sub SetShapesMacros()
        Dim ws     As Worksheet
        Dim i      As Integer
        Dim iShape As Shape
        
        Set ws = Worksheets("Macro")
        For i = 3 To ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
            Set iShape = ws.Shapes(ws.Cells(i, "E").Value2)
            iShape.OnAction = ws.Cells(i, "D").Value2
        Next i
    End Sub
    
    


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    • Marked as answer by Sam_N8 Saturday, June 25, 2016 9:10 PM
    Saturday, June 25, 2016 8:17 PM
  • Hi Felipe,

    thank you for helping again.

    As far as I know it worked.

    it also gave the error

    the Index in the specified collection is out of bounds

    However so long as it does the job, I'm happy

    Should I be worried about this error?

    Thank you very much

    sam

    Saturday, June 25, 2016 9:10 PM
  • Probably you got this error because one of your shape's name listed on column E doesn't exist in your worksheet.

    You should get worried because the code stops at the line where you got the error and doesn't make the associations of the following lines. So, it might do only half of the job.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Sunday, June 26, 2016 2:43 PM