Generate an event when a cell is pressed

  • Thread starter Thread starter irfan
  • Start date Start date
I

irfan

Hello All,

I want to generate an event when a user presses a key on the keyboard
or in other words if a user changes the content of a cell.

What i actually want to do is to ask a user for password if a user
changes
the contents of a cell.
Contents of a cell can be changed either by doublecliking by mouse or
directly pressing the keys. I can handle former by using
beforedoubleclick event and then ask user the password, but i am not
able to control the keypress event.

is there any keypress event or other way to do this. Any help is
appreciated.

TIA

Irfan
 
Hi Irfan

No, when you start writing in a cell, Excel enters "insert" mode and no
event or code runs. But with a little awkward programming you can perhaps
authorize the changes afterwards. Here's a humble start, put into the sheet
module:

Option Explicit

Dim LastCel As Range
Dim LastContent As String

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not LastCel Is Nothing Then
If LastCel.Formula <> LastContent Then
If InputBox("Password:") <> "Pwd" Then _
LastCel.Formula = LastContent
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set LastCel = Target(1)
LastContent = LastCel.Formula
End Sub
 
Here's an adaptation from a post from Tom Ogilvy that may be
appropriate to your situation.

http://www.google.com/groups?hl=en&...fe=off&[email protected]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strNew As String, strVal As String
On Error GoTo errHandler
If Target.Count > 1 Then Exit Sub
If Target.Column = 3 Then
strNew = Target.Value
If strNew = "" Then Exit Sub
Application.EnableEvents = False
Application.Undo
strVal = Target.Value
If InputBox("Password:") = "YourPassword" Then _
Target.Value = strNew
End If
errHandler:
Application.EnableEvents = True
End Sub

Tested using Excel 97SR2 on Windows 98SE,

HTH
Paul
 
Back
Top