Ronald...

This works for me

Sub MacroCalledFromRectangle()
Dim s As Shape
Dim r As Range
Set s = ActiveSheet.Shapes(Application.Caller)
Set r = s.TopLeftCell
End Sub

HTH


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Ronald Moolenaar) wrote:

Hi,

For my sheet, I have followed John Walkenbachs tip to place rectangles
over cells and assign macro's to them to get a 'single click handler'
as described in http://j-walk.com/ss/excel/tips/tip12.htm

I have done this for a range of 12 rows and 12 columns. When one of
the cells is clicked, I show a userform where the user can enter some
data, I perform some calculations and the result must be placed in the
cell under the rectangle. Now when the macro is called, I want to know
which rectangle was clicked, or more important, which cell is
underneath! Offcourse I don't want to create 144 macro's to handle all
cells separately. So, how can I determine (in my macro) which
rectangle was clicked and find the underlying cell?

Here is my code:

' Adds rectangles to range
Sub AddRectangle()
Dim cell As Range

If Worksheets(1).ProtectDrawingObjects Then
MsgBox "Unprotect the sheet first."
Exit Sub
End If

' Maak vierkant aan
For Each cell In Range("Uitslagen")
Worksheets(1).Rectangles.Add(cell.Left, cell.Top, cell.Width,
cell.Height).Select
Selection.Interior.ColorIndex = xlNone
Selection.Border.LineStyle = xlNone
Selection.OnAction = "ShowResultDialog"
Next
End Sub

Sub ShowResultDialog()
' TODO: Find clicked rectangle
' TODO: Find cell under clicked rectangle
UserFormSetResult.Show
End Sub


Thanks for any suggestions.
Ronald