Making a connection: shape clicked and cell it lies over.

[Gazza]

Hi,
I'm a relative newcomer to VB, but I'm stuck on the following problem:
I have a 10 x 10 multiplaction square, than when each square is clicked
on, the font changes colour, and the answer appears. To assign the macro
though, I created 100 autoshape rectangles and lined them up over my
10x10 squares, then used code such as:

Sub Rectangle1_Click()
Range("B2").Select
Call Color_Change
End Sub

Sub Rectangle2_Click()
Range("C2").Select
Call Color_Change
End Sub

Sub Rectangle3_Click()
Range("D2").Select
Call Color_Change
End Sub
....
Sub Color_Change()
cell(va, va).Select
col = Selection.Font.ColorIndex
If col = 1 Then
Selection.Font.ColorIndex = 2
Else
Selection.Font.ColorIndex = 1
End If
End Sub

Now the range().select is hard-coded into each sub (giving a lot of
subs), and I can't work out how to get a connection between the shape
that was clicked (even by using topleftcell.address) and the cell I need
to change the font in. Surely there's a neater way of doing what I've
got?

Thanks

 

[Tom Ogilvy]

Assign the following to all you rectangles. (assuming the are objects of
type rectangle).

Sub AnyRectangle_Click()
dim r as Rectangle
dim rng as Range, col as Long
set r = activesheet.Rectangles(application.Caller)
set rng = r.topLeftCell
col = rng.Font.ColorIndex
If col = 1 Then
rng.Font.ColorIndex = 2
Else
rng.Font.ColorIndex = 1
End If
End Sub

 

[finder]

Hi

I have a similar problem- I have images(GIFs) on my Excel worksheet with macros assigned to. I want to forward a value to the macro according to the image that was clicked (the name of the image or The TopLeft.Cell.Name). My experience was not enough to successfully modify the macro below though. Can anyone help me out

----- Tom Ogilvy wrote: ----

Assign the following to all you rectangles. (assuming the are objects o
type rectangle)

Sub AnyRectangle_Click(
dim r as Rectangl
dim rng as Range, col as Lon
set r = activesheet.Rectangles(application.Caller
set rng = r.topLeftCel
col = rng.Font.ColorInde
If col = 1 The
rng.Font.ColorIndex =
Els
rng.Font.ColorIndex =
End I
End Su