[ HELP ] VBA In a user form, a problem occur after I used [ SendKeys "{ }"]

%3CLINGO-SUB%20id%3D%22lingo-sub-1542992%22%20slang%3D%22en-US%22%3E%5B%20HELP%20%5D%20VBA%20In%20a%20user%20form%2C%20a%20problem%20occur%20after%20I%20used%20%5B%20SendKeys%20%26amp%3Bquot%3B%7B%20%7D%26amp%3Bquot%3B%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1542992%22%20slang%3D%22en-US%22%3E%3CP%3EVBA%20In%20a%20user%20form%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20%5B%20SendKeys%20%22%7B%20%7D%22%5D%2C%3C%2FP%3E%3CP%3EThen%3C%2FP%3E%3CP%3ENumLock%26nbsp%3B%20AUTO%20DROP%20sometime%20%3D%26nbsp%3B%20%26nbsp%3B%3D%26nbsp%3B%3C%2FP%3E%3CP%3EHOW%20TO%20%3CSPAN%3Eprevent%26nbsp%3B%3C%2FSPAN%3ETHAT%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1542992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543110%22%20slang%3D%22en-US%22%3ERe%3A%20%5B%20HELP%20%5D%20VBA%20In%20a%20user%20form%2C%20a%20problem%20occur%20after%20I%20used%20%5B%20SendKeys%20%26amp%3Bquot%3B%7B%20%7D%26amp%3Bquot%3B%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543110%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F578919%22%20target%3D%22_blank%22%3E%40adlih%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20insert%20a%20Class%20Module%20and%20name%20it%20%3CSTRONG%3ENumLockClass%3C%2FSTRONG%3E%20and%20then%20place%20the%20following%20code%20into%20the%20Class%20Module.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%23If%20VBA7%20And%20Win64%20Then%0A%20%20%20%20Private%20Declare%20PtrSafe%20Function%20GetVersionEx%20Lib%20%22Kernel32%22%20_%0A%20%20%20%20%20%20%20%20Alias%20%22GetVersionExA%22%20_%0A%20%20%20%20%20%20%20%20(lpVersionInformation%20As%20OSVERSIONINFO)%20As%20Long%0A%0A%20%20%20%20Private%20Declare%20PtrSafe%20Sub%20keybd_event%20Lib%20%22user32%22%20_%0A%20%20%20%20%20%20%20%20(ByVal%20bVk%20As%20Byte%2C%20_%0A%20%20%20%20%20%20%20%20ByVal%20bScan%20As%20Byte%2C%20_%0A%20%20%20%20%20%20%20%20ByVal%20dwflags%20As%20Long%2C%20ByVal%20dwExtraInfo%20As%20Long)%0A%0A%20%20%20%20Private%20Declare%20PtrSafe%20Function%20GetKeyboardState%20Lib%20%22user32%22%20_%0A%20%20%20%20%20%20%20%20(pbKeyState%20As%20Byte)%20As%20Long%0A%0A%20%20%20%20Private%20Declare%20PtrSafe%20Function%20SetKeyboardState%20Lib%20%22user32%22%20_%0A%20%20%20%20%20%20%20%20(lppbKeyState%20As%20Byte)%20As%20Long%0A%23Else%0A%20%20%20%20Private%20Declare%20PtrSafe%20Function%20GetVersionEx%20Lib%20%22Kernel32%22%20_%0A%20%20%20%20%20%20%20%20Alias%20%22GetVersionExA%22%20_%0A%20%20%20%20%20%20%20%20(lpVersionInformation%20As%20OSVERSIONINFO)%20As%20Long%0A%0A%20%20%20%20Private%20Declare%20PtrSafe%20Sub%20keybd_event%20Lib%20%22user32%22%20_%0A%20%20%20%20%20%20%20%20(ByVal%20bVk%20As%20Byte%2C%20_%0A%20%20%20%20%20%20%20%20ByVal%20bScan%20As%20Byte%2C%20_%0A%20%20%20%20%20%20%20%20ByVal%20dwflags%20As%20Long%2C%20ByVal%20dwExtraInfo%20As%20Long)%0A%0A%20%20%20%20Private%20Declare%20PtrSafe%20Function%20GetKeyboardState%20Lib%20%22user32%22%20_%0A%20%20%20%20%20%20%20%20(pbKeyState%20As%20Byte)%20As%20Long%0A%0A%20%20%20%20Private%20Declare%20PtrSafe%20Function%20SetKeyboardState%20Lib%20%22user32%22%20_%0A%20%20%20%20%20%20%20%20(lppbKeyState%20As%20Byte)%20As%20Long%0A%23End%20If%0A%0A'%20Type%20declaration%0APrivate%20Type%20OSVERSIONINFO%0A%20%20%20%20dwOSVersionInfoSize%20As%20Long%0A%20%20%20%20dwMajorVersion%20As%20Long%0A%20%20%20%20dwMinorVersion%20As%20Long%0A%20%20%20%20dwBuildNumber%20As%20Long%0A%20%20%20%20dwPlatformId%20As%20Long%0A%20%20%20%20szCSDVersion%20As%20String%20*%20128%0AEnd%20Type%0A%0A%0A'Constant%20declarations%0AConst%20VK_NUMLOCK%20%3D%20%26amp%3BH90%0AConst%20VK_SCROLL%20%3D%20%26amp%3BH91%0AConst%20VK_CAPITAL%20%3D%20%26amp%3BH14%0AConst%20KEYEVENTF_EXTENDEDKEY%20%3D%20%26amp%3BH1%0AConst%20KEYEVENTF_KEYUP%20%3D%20%26amp%3BH2%0A%0AProperty%20Get%20value()%20As%20Boolean%0A'%20%20%20Get%20the%20current%20state%0A%20%20%20%20Dim%20keys(0%20To%20255)%20As%20Byte%0A%20%20%20%20GetKeyboardState%20keys(0)%0A%20%20%20%20value%20%3D%20keys(VK_NUMLOCK)%0AEnd%20Property%0A%0AProperty%20Let%20value(boolVal%20As%20Boolean)%0A%20%20%20%20Dim%20o%20As%20OSVERSIONINFO%0A%20%20%20%20Dim%20keys(0%20To%20255)%20As%20Byte%0A%20%20%20%20o.dwOSVersionInfoSize%20%3D%20Len(o)%0A%20%20%20%20GetVersionEx%20o%0A%20%20%20%20GetKeyboardState%20keys(0)%0A'%20%20%20Is%20it%20already%20in%20that%20state%3F%0A%20%20%20%20If%20boolVal%20%3D%20True%20And%20keys(VK_NUMLOCK)%20%3D%201%20Then%20Exit%20Property%0A%20%20%20%20If%20boolVal%20%3D%20False%20And%20keys(VK_NUMLOCK)%20%3D%200%20Then%20Exit%20Property%0A'%20%20%20Toggle%20it%0A%20%20%20%20'Simulate%20Key%20Press%0A%20%20%20%20keybd_event%20VK_NUMLOCK%2C%20%26amp%3BH45%2C%20KEYEVENTF_EXTENDEDKEY%20Or%200%2C%200%0A%20%20%20%20'Simulate%20Key%20Release%0A%20%20%20%20keybd_event%20VK_NUMLOCK%2C%20%26amp%3BH45%2C%20KEYEVENTF_EXTENDEDKEY%20Or%20_%0A%20%20%20%20%20%20KEYEVENTF_KEYUP%2C%200%0AEnd%20Property%0A%0ASub%20Toggle()%0A'%20%20%20Toggles%20the%20state%0A%20%20%20%20Dim%20o%20As%20OSVERSIONINFO%0A%20%20%20%20o.dwOSVersionInfoSize%20%3D%20Len(o)%0A%20%20%20%20GetVersionEx%20o%0A%20%20%20%20Dim%20keys(0%20To%20255)%20As%20Byte%0A%20%20%20%20GetKeyboardState%20keys(0)%0A%20%20%20%20'Simulate%20Key%20Press%0A%20%20%20%20keybd_event%20VK_NUMLOCK%2C%20%26amp%3BH45%2C%20KEYEVENTF_EXTENDEDKEY%20Or%200%2C%200%0A%20%20%20%20'Simulate%20Key%20Release%0A%20%20%20%20keybd_event%20VK_NUMLOCK%2C%20%26amp%3BH45%2C%20KEYEVENTF_EXTENDEDKEY%20Or%20_%0A%20%20%20%20%20%20KEYEVENTF_KEYUP%2C%200%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20in%20your%20main%20code%20%3CSTRONG%3Ebefore%20End%20Sub%3C%2FSTRONG%3E%20check%20the%20status%20of%20NumLock%20with%20the%20following%20lines.%20This%20will%20turn%20on%20the%20NumLock%20it%20is%20turned%20off.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EDim%20numLock%20As%20New%20NumLockClass%0AIf%20numLock.value%20%3D%20False%20Then%20numLock.value%20%3D%20True%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543119%22%20slang%3D%22en-US%22%3ERe%3A%20%5B%20HELP%20%5D%20VBA%20In%20a%20user%20form%2C%20a%20problem%20occur%20after%20I%20used%20%5B%20SendKeys%20%26amp%3Bquot%3B%7B%20%7D%26amp%3Bquot%3B%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543119%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F578919%22%20target%3D%22_blank%22%3E%40adlih%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERefer%20to%20the%20following%20screenshot%20to%20know%20how%20to%20rename%20a%20Class%20Module.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Class%20Module.jpg%22%20style%3D%22width%3A%20261px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207607iDA01F5B1567CF0BB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Class%20Module.jpg%22%20alt%3D%22Class%20Module.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547047%22%20slang%3D%22en-US%22%3ERe%3A%20%5B%20HELP%20%5D%20VBA%20In%20a%20user%20form%2C%20a%20problem%20occur%20after%20I%20used%20%5B%20SendKeys%20%26amp%3Bquot%3B%7B%20%7D%26amp%3Bquot%3B%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547047%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20m%3CSPAN%3Eessage%2C%3CBR%20%2F%3E%3C%2FSPAN%3Ebut%20the%26nbsp%3Bproblem%20still%20occur%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMY%20MAIN%20CODE%20HERE%3A%3C%2FP%3E%3CP%3EPrivate%20Sub%20TextBox1_KeyDown(ByVal%20KeyCode%20As%20MSForms.ReturnInteger%2C%20ByVal%20Shift%20As%20Integer)%3CBR%20%2F%3EIf%20KeyCode%20%3D%2013%20Then%3CBR%20%2F%3ECommandButton1_Click%3CBR%20%2F%3ESendKeys%20%22%7BLEFT%7D%22%3CBR%20%2F%3EDim%20numLock%20As%20New%20NumLockClass%3CBR%20%2F%3EIf%20numLock.value%20%3D%20False%20Then%20numLock.value%20%3D%20True%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1547675%22%20slang%3D%22en-US%22%3ERe%3A%20%5B%20HELP%20%5D%20VBA%20In%20a%20user%20form%2C%20a%20problem%20occur%20after%20I%20used%20%5B%20SendKeys%20%26amp%3Bquot%3B%7B%20%7D%26amp%3Bquot%3B%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1547675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F578919%22%20target%3D%22_blank%22%3E%40adlih%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20right.%20After%20testing%20it%20multiple%20times%20I%20also%20found%20that%20the%20numlock%20gets%20turned%20off%20after%20regular%20intervals.%20SendKeys%20are%20notorious%20for%20this%20weird%20behavior.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20let%20you%20know%20if%20I%20come%20across%20any%20workaround%20for%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1552230%22%20slang%3D%22en-US%22%3ERe%3A%20%5B%20HELP%20%5D%20VBA%20In%20a%20user%20form%2C%20a%20problem%20occur%20after%20I%20used%20%5B%20SendKeys%20%26amp%3Bquot%3B%7B%20%7D%26amp%3Bquot%3B%5D%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1552230%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you~%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

VBA In a user form, 

I used [ SendKeys "{ }"],

Then

NumLock  AUTO DROP sometime =   = 

HOW TO prevent THAT? 

 

 

5 Replies
Highlighted

@adlih 

 

Please insert a Class Module and name it NumLockClass and then place the following code into the Class Module.

#If VBA7 And Win64 Then
    Private Declare PtrSafe Function GetVersionEx Lib "Kernel32" _
        Alias "GetVersionExA" _
        (lpVersionInformation As OSVERSIONINFO) As Long

    Private Declare PtrSafe Sub keybd_event Lib "user32" _
        (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwflags As Long, ByVal dwExtraInfo As Long)

    Private Declare PtrSafe Function GetKeyboardState Lib "user32" _
        (pbKeyState As Byte) As Long

    Private Declare PtrSafe Function SetKeyboardState Lib "user32" _
        (lppbKeyState As Byte) As Long
#Else
    Private Declare PtrSafe Function GetVersionEx Lib "Kernel32" _
        Alias "GetVersionExA" _
        (lpVersionInformation As OSVERSIONINFO) As Long

    Private Declare PtrSafe Sub keybd_event Lib "user32" _
        (ByVal bVk As Byte, _
        ByVal bScan As Byte, _
        ByVal dwflags As Long, ByVal dwExtraInfo As Long)

    Private Declare PtrSafe Function GetKeyboardState Lib "user32" _
        (pbKeyState As Byte) As Long

    Private Declare PtrSafe Function SetKeyboardState Lib "user32" _
        (lppbKeyState As Byte) As Long
#End If

' Type declaration
Private Type OSVERSIONINFO
    dwOSVersionInfoSize As Long
    dwMajorVersion As Long
    dwMinorVersion As Long
    dwBuildNumber As Long
    dwPlatformId As Long
    szCSDVersion As String * 128
End Type


'Constant declarations
Const VK_NUMLOCK = &H90
Const VK_SCROLL = &H91
Const VK_CAPITAL = &H14
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2

Property Get value() As Boolean
'   Get the current state
    Dim keys(0 To 255) As Byte
    GetKeyboardState keys(0)
    value = keys(VK_NUMLOCK)
End Property

Property Let value(boolVal As Boolean)
    Dim o As OSVERSIONINFO
    Dim keys(0 To 255) As Byte
    o.dwOSVersionInfoSize = Len(o)
    GetVersionEx o
    GetKeyboardState keys(0)
'   Is it already in that state?
    If boolVal = True And keys(VK_NUMLOCK) = 1 Then Exit Property
    If boolVal = False And keys(VK_NUMLOCK) = 0 Then Exit Property
'   Toggle it
    'Simulate Key Press
    keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
    'Simulate Key Release
    keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or _
      KEYEVENTF_KEYUP, 0
End Property

Sub Toggle()
'   Toggles the state
    Dim o As OSVERSIONINFO
    o.dwOSVersionInfoSize = Len(o)
    GetVersionEx o
    Dim keys(0 To 255) As Byte
    GetKeyboardState keys(0)
    'Simulate Key Press
    keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
    'Simulate Key Release
    keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or _
      KEYEVENTF_KEYUP, 0
End Sub

 

Then in your main code before End Sub check the status of NumLock with the following lines. This will turn on the NumLock it is turned off.

Dim numLock As New NumLockClass
If numLock.value = False Then numLock.value = True

 

 

 

Highlighted

@adlih 

Refer to the following screenshot to know how to rename a Class Module.

Class Module.jpg

Highlighted

Thank you for your message,
but the problem still occur @Subodh_Tiwari_sktneer 

 

MY MAIN CODE HERE:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
CommandButton1_Click
SendKeys "{LEFT}"
Dim numLock As New NumLockClass
If numLock.value = False Then numLock.value = True
End If
End Sub

Highlighted

@adlih 

You are right. After testing it multiple times I also found that the numlock gets turned off after regular intervals. SendKeys are notorious for this weird behavior. 

I will let you know if I come across any workaround for this.