Good evening,

I would like to create a table in Excel, for simplicity, let’s say 4x4. The table could occupy cells D4:G8. The cells would be numbered 1 thru 16. What I am trying to accomplish is using VBA whenever any of the cells are selected the selected number would appear in another cell, let’s say A2, just like it appears in the formula bar. This may not be far too difficult to solve because no one has been able to code it and I must confess that don't have a clue where to start.

Anyway, thanks for anyone’s help to try and solve the code.

noway

noway wrote:

I would like to create a table in Excel, for simplicity, let’s say 4x4.
The table could occupy cells D4:G8. The cells would be numbered 1 thru
16. What I am trying to accomplish is using VBA whenever any of the
cells are selected the selected number would appear in another cell,
let’s say A2, just like it appears in the formula bar. This may not be
far too difficult to solve because no one has been able to code it and I
must confess that don't have a clue where to start.

Anyway, thanks for anyone’s help to try and solve the code.

Put this in the sheet's class in the VBA editor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const TOPROW = 4
Const BOTTOMROW = 8
Const LEFTCOL = 4
Const RIGHTCOL = 7
Select Case Target.Row
Case TOPROW To BOTTOMROW
Select Case Target.Column
Case LEFTCOL To RIGHTCOL
Range("A2").Value = ((Target.Row - TOPROW) * _
(RIGHTCOL - LEFTCOL + 1)) + _
Target.Column - LEFTCOL + 1
End Select
End Select
End Sub

Note that using D4:G8 results in a grid of 20 cells, not 16. Adjust the above
constants as desired. (For a grid of 16 cells, set BOTTOMROW = 7.)

Now give me a hard one!

--
I have known few men who were noble,
for nobility is scarcer far than heroism.

Excellent, it worked just as you said and Thanks a Million.

Auric,

I have hit a snag. The numbers in my table are not consecutive.
They are like the following with several more as the table is larger.

48 43 4 8
34 12 20 1
40 16 18 23
17 11 50 19

Having odd numbers may be where the difficulty lies. Any hope for this arrangement?

Thanks,