Help Needed

[noway]

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 thr
16. What I am trying to accomplish is using VBA whenever any of th
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 b
far too difficult to solve because no one has been able to code it and
must confess that don't have a clue where to start.

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

nowa

 

[Auric__]

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!

 

[noway]

noway wrote:
-

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 th
above
constants as desired. (For a grid of 16 cells, set BOTTOMROW = 7.)

Now give me a hard one!

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