Run Excel Macro on Enter Cell 1

[pheos2]

Is there a way to run a macro when a user clicks in a cell to enter data?

I want to display an instruction in a frozen pane at the top of the screen. So when the user clicks in a cell a macro is run that, for example, changes the value of cell A1 to "Type a Description here".
Obviously there will be different instructions for different cells.

Iv had had a look round on goolge but cant seem to find the right thing.
Any ideas?

 

[SkipVought]

Hi,

Excel cannot read the user's mind to know what the INTENT of a selection change.

BUT, Excel does have several Worksheet Events, among which are the Worksheet_SelectionChange and Worksheet_Change events.

So let's say that when the user selects A5, then "Type a Description here" appears in A1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  with target
    if .address = "A5" then
      [A1] = "Type a Description here"
    else
      [A1].clearcontents
    end if
  end with
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[waynerenaud]

You can also try something like this. (You can change the msgbox line to call a macro instead ie module1.macro1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = "$A$5" Then
Range("A1").Value = "Type a description here"
MsgBox "you clicked cell A5"
End If
End Sub

 

[pheos2]

Thanks Guys, Thats Just what I wanted