A formula for displaying the cell reference of the selected cell

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200603/1

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

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


"Emlou85 via OfficeKB.com" <u19317@uwe> wrote in message
news:5d73183e22541@uwe...
>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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200603/1

Thank you very much for your help!

Emma

Trevor Shuttleworth wrote:
>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
>[quoted text clipped - 8 lines]
>>
>> Emma

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200603/1