Thread: macro in cell

Hi, I was wondering if there was a way to have a Macro associated with a cell. I have 20 cells that have pictures in them, and if you click on the picture, the macro activate. However, there are 20 macros, all of which are the same except for the cell address. If I could make a macro associated with a cell, I could use ActiveCell.FormulaR1C1 to directly edit that cell, and save memory.
So, is there any way to make a macro associated / part of a cell?

Okay, so I have posted an example. For each of the 20 cells in the D column (not the 6 cells in row 25) have an UpD## macro, which simply increments them. The macro associated with B1 increments B1 and moves the cursor to D25. I would like there to be only one UpD macro, and it's dependent on what cell you select. If it's possible, I'd also like the UpB1SelectD25 macro not be based in a picture.

Hello nesthead98,

Pictures and other objects are not in the cell. They are displayed over the cell(s) on the Excel Drawing Layer. If the picture is covering the cell then what is the user editing?

Hello nesthead98,

It can be done. If you post your workbook, it will be easier and faster to answer your question.

Hello nesthead98,

The code below has been added to the attached workbook. This will increment the cell count when the picture is clicked.

Code:

Sub UpD03()
  IncCount
End Sub

Sub UpB1SelectD25()
  IncCount
End Sub

Sub IncCount()

  Dim Shp As Shape
  
    Set Shp = ActiveSheet.Shapes(Application.Caller)
      With Shp
        R = (.TopLeftCell.Row + .BottomRightCell.Row) \ 2
        Cells(R, .TopLeftCell.Column).Value = Cells(R, .TopLeftCell.Column) + 1
      End With
      
End Sub

Hello neasthead98,

The key to this macro being used by all the "pictures" is identifying which picture was clicked. Excel has a property named Caller that determines how VBA was called from Excel. This property may either be the name of an object or the object itself depending on how Excel invoked the VBA code.

In this case, the name of the picture, which is actually an AutoShape object, is returned by Application.Caller when the shape is clicked. An object variable is then assigned to the clicked shape.

Code:

  Set Shp = ActiveSheet.Shapes(Application.Caller)

This object variable Shp now allows VBA to access the properties and methods of the clicked shape. Objects are referenced in VBA by 2 points: the Upper Left Cell and the Lower Right Cell that the object occupies. Since placement is seldom done precisely, the code takes the average of the current placement to determine the which row the shape is on. the variable R is the row and Cells(R, .TopLeftCell.Column) is the address of the cell next to the "picture". One is then added to contents of this cell.

Code:

      With Shp
        R = (.TopLeftCell.Row + .BottomRightCell.Row) \ 2
        Cells(R, .TopLeftCell.Column).Value = Cells(R, .TopLeftCell.Column) + 1
      End With