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

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

Hi, Ive tried this but it doesnt seem to produce a range
as the return value ? how do you get the address of the
cell from this ?


-----Original Message-----
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


.

Application.Caller, that's it! Thanks a lot.

Ronald

keepitcool wrote in message .. .
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