lock excel worksheet when loosing focus

  • Thread starter Thread starter Nader
  • Start date Start date
N

Nader

Hello,

I would like to lock my excel worksheet as soon as I loose focus. An exemple
: Let's I'm puting values into my sheet and then I decide to write a word
document. Once I clicked on "Start" (Programs->etc...), I want my excel to
lock itself and then if I go back to my excel sheet I will be asked for a
password.

Is there a lost focus on worksheet method in excel vba ?

Thanks in advance

Nader

PS : all this is to avoid to put in value by mistake in my excel sheet
 
Excel cannot you this directly as there is no Application_LostFocus event.
You will have use some API calls within a Timer to see if Excel is the
ForeGround window.

NickHK
 
But can I use a Timer inside excel vba to lock my excel sheet if a user do
not enter a value after 20 seconds ?
 
Nader,

Not what you originally asked for but may be an acceptable 'simple'
solution for you...
PS : all this is to avoid to put in value by mistake in my excel sheet
Click to expand...

Try putting the following procedure in your worksheet code module. It
will mean hitting Return twice for every legitimate entry but may be an
acceptable overhead to avoid an accidental entry...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Response As Variant
Dim msg As String
Dim Title As String

msg = Target.Range("A1").Address & " = '" &
Target.Range("A1").Value & "'"
msg = msg & vbCrLf & vbCrLf
msg = msg & "Accept entry?"

Title = "Confirm Entry"

Response = MsgBox(msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End Sub

Kind regards,
NickH
 
Oops, beware the wrap-around:

Target.Range("A1").Value & "'"

belongs on the end of the preceding line.

Nick H
 
thanks for your help Nick !

NickH said:
Nader,

Not what you originally asked for but may be an acceptable 'simple'
solution for you...


Try putting the following procedure in your worksheet code module. It
will mean hitting Return twice for every legitimate entry but may be an
acceptable overhead to avoid an accidental entry...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Response As Variant
Dim msg As String
Dim Title As String

msg = Target.Range("A1").Address & " = '" &
Target.Range("A1").Value & "'"
msg = msg & vbCrLf & vbCrLf
msg = msg & "Accept entry?"

Title = "Confirm Entry"

Response = MsgBox(msg, vbYesNo + vbQuestion, Title)

If Response = vbNo Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
End Sub

Kind regards,
NickH
Click to expand...
 
Back
Top