Missing "Worksheet_BeforeEdit" Event !

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,633
Office Version
  1. 2016
Platform
  1. Windows
Is there an easy way to detect when XL enters on Edit Mode ?

I would like to display a message box whenever the user is about to edit a particular worksheet range .

Regards.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Do you mean the Worksheet_Change event?

And are you sure you want to pop up a MsgBox every time? Could get rather annoying, couldn't it?! ;)
 
Upvote 0
Do you mean the Worksheet_Change event?

And are you sure you want to pop up a MsgBox every time? Could get rather annoying, couldn't it?! ;)

No. I mean BEFORE the contents of a cell are actually changed.
The Msgbox would be displayed only once at the first hit of a key.

Regards.
 
Upvote 0
Ah I see. Not as far as I know, but you may be able to do something using a Class Module and a "WithEvents Excel.Application" object. Don't know what the event would be called, though, if it exists at all.
 
Upvote 0
Another Jaafarian post! :) Code will not "usually" run when Excel is in edit mode. It's basically "Application.Busy = True" when in edit mode. You would have to resort to a timer or some other hack. I would simply let the user type what they wish and then respond afterwards. You posted code awhile back that used a capture for a simulated worksheet keypress event... Didn't you?
 
Upvote 0
Thanks Mike and Tom.

I am halfway there (see code below)

However, I want to be able to encapsulate the code in the worksheet module so It can be called following the same pattern as other worksheet builtin events.

I am experimenting with Withevents; RaiseEvent; and Event keywords but with no luck so far .

Code in a Standard Module :

Code:
Declare Function SetWindowsHookEx Lib _
"user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, _
ByVal hmod As Long, ByVal dwThreadId As Long) As Long

Declare Function CallNextHookEx Lib "user32" _
(ByVal hHook As Long, ByVal nCode As Long, ByVal wParam As Long, lParam As Any) As Long

Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long

Declare Function GetActiveWindow Lib "user32" () As Long

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Declare Sub keybd_event Lib "user32.dll" _
(ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Declare Function IsCharAlphaNumeric Lib "user32" Alias "IsCharAlphaNumericA" _
(ByVal cChar As Byte) As Long

Const HC_ACTION = 0
Const WM_KEYDOWN = &H100
Const WH_KEYBOARD_LL = 13
Dim hhkLowLevelKybd As Long
Dim blnHookEnabled As Boolean
Const vkCode = &H71
Type KBDLLHOOKSTRUCT
    vkCode As Long
    scanCode As Long
    flags As Long
    time As Long
    dwExtraInfo As Long
End Type

Function LowLevelKeyboardProc _
(ByVal nCode As Long, ByVal wParam As Long, lParam As KBDLLHOOKSTRUCT) As Long
    On Error Resume Next
    If GetActiveWindow = FindWindow("XLMAIN", Application.Caption) Then
        If (nCode = HC_ACTION) Then
            If wParam = WM_KEYDOWN Then
                If IsCharAlphaNumeric(lParam.vkCode) Then
                    Unhook_KeyBoard
                    If vbYes = MsgBox("You are about to edit " & ActiveCell.Address & _
                     vbCrLf & "Continue ?", vbInformation + vbYesNo) Then
                        ActiveCell = Chr(lParam.vkCode)
                        keybd_event vkCode, 0, 0, 0
                    Else
                        Hook_KeyBoard
                    End If
                End If
            End If
        End If
    End If
    LowLevelKeyboardProc = CallNextHookEx(0, nCode, wParam, ByVal lParam)
End Function
            
            
Sub Hook_KeyBoard()
'\don't hook the keyboard twice !!
    If blnHookEnabled = False Then
        hhkLowLevelKybd = SetWindowsHookEx _
        (WH_KEYBOARD_LL, AddressOf LowLevelKeyboardProc, Application.Hinstance, 0)
        blnHookEnabled = True
    End If
End Sub
          
            
Sub Unhook_KeyBoard()
    If hhkLowLevelKybd <> 0 Then UnhookWindowsHookEx hhkLowLevelKybd
    blnHookEnabled = False
End Sub

Code in the Worksheet Module :

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Range("a1").Address Then
        Hook_KeyBoard
    Else
        Unhook_KeyBoard
    End If
End Sub

Now, try editing Cell A1 and you will get a MsgBox before you can actually change the cell.


Can anybody make this work by creating a new event that would be called Worksheet_BeforeEdit ?

Regards.
 
Upvote 0

Forum statistics

Threads
1,215,766
Messages
6,126,758
Members
449,336
Latest member
p17tootie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top