Generate an event when a cell is pressed

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
 
H

Harald Staff

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
 
P

pfsardella

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
 
Top