A formula for displaying the cell reference of the selected cell

  • Thread starter Emlou85 via OfficeKB.com
  • Start date
E

Emlou85 via OfficeKB.com

I have drawn a keyboard in excel (B1 is note C, C2 is note D etc). The user
is told to click on the note
E. I then have an If statement that says if cell A2 displays D1 (ie note E)
then to say 'well done that is correct' and if not then display 'wrong have
another go'. My problem is getting a formula in A2 which displays what has
been selected, so the If statement can work!

Emma
 
K

Ken Johnson

Hi Emma,
as far as I know there isn't a formula that will place the selected
cell's value into A1.
However, if you copy the code below, then right click that worksheet's
Sheet Tab a popup contextual menu appears.
Select "View Code" then paste the copied code into the Sheet's code
module, which is the large white space just below the headings
"(General)" on the left and "(Declarations)" on the right.
Then go Alt + F11 to get back to the worksheet.
Every time you select a different cell on hat sheet the code is run and
the value in A1 will be the value in the selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells(1, 1) = Target.Value
End Sub

Ken Johnson
 
T

Trevor Shuttleworth

Emma

I think you will have to use a macro. Assuming the keyboard is in Sheet 1,
the Sheet 1 Class Module needs the following Selection Change code:

' Sheet 1 Class Module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Range("A2") = Target.Address(False, False)
Application.EnableEvents = True
End Sub

Whenever a cell is selected, its address will be put into cell A2

Regards

Trevor
 
E

Emlou85 via OfficeKB.com

Thank you very much for your help!

Emma

Trevor said:
Emma

I think you will have to use a macro. Assuming the keyboard is in Sheet 1,
the Sheet 1 Class Module needs the following Selection Change code:

' Sheet 1 Class Module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Range("A2") = Target.Address(False, False)
Application.EnableEvents = True
End Sub

Whenever a cell is selected, its address will be put into cell A2

Regards

Trevor
I have drawn a keyboard in excel (B1 is note C, C2 is note D etc). The
user
Click to expand...
[quoted text clipped - 8 lines]
Emma
Click to expand...
Click to expand...
 
Top