VBA Remember Numlock State

[Legacy 143009]

Hi Guys!

I have a macro that formats a UserForm TextBox. If there is a character out of format, then it sends backspace key. But somehow, it turns off the numlock sometimes. Now what i need is something which will recall the previous numlock state. Like;

NState = Numlock Key Status

'My Code goes here

Numlock Key Status = NState

Thanks a lot!

 

[hippiehacker]

copy this to a regular module

Public Const VK_NUMLOCK = &H90

Public Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Long


Sub Test()
MsgBox "Numlock is " & IIf(GetKeyState(VK_NUMLOCK) = 1, "On", "Off")
End Sub

 

[Legacy 143009]

Sorry, I couldn't make it work.

 

[RoryA]

Simpler solution - don't use Sendkeys.

 

[Legacy 143009]

Then how to remove irrelevant characters as they are typed?

 

[pcc]

adapt this to suit. it will only allow numbers to be entered , plus one period(.)

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc(".") ' only allow one decimal point to be used
        If InStr(1, Me.TextBox1.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select

End Sub

 

[Legacy 143009]

adapt this to suit. it will only allow numbers to be entered , plus one period(.)

(-) minus, too?
Oh I see how to modify.

 

[RoryA]

Personally I just test if it's numeric after the user has finished entering data.

 

[pcc]

Case Asc("0") To Asc("9"), Asc("-")


will allow a minus as well, yes

 

[eikogs]

copy this to a regular module

Public Const VK_NUMLOCK = &H90

Public Declare Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Long


Sub Test()
MsgBox "Numlock is " & IIf(GetKeyState(VK_NUMLOCK) = 1, "On", "Off")
End Sub

I used a version of this code to work around the numlock issue with "SendKeys". It works great on 32 bit installations of excel, but I have users using my workbook on both 32 bit and 64 bit installations. I used "PtrSafe" in my declaration which at least allows the file to open in 64 bit installations, but I still have the numlock toggling on and off while my code runs and not getting left in the desired state. Here is my code:

Public Declare PtrSafe Function GetKeyState Lib "user32" _
(ByVal nVirtKey As Long) As Long
Public Const VK_NUMLOCK = &H90


NumLockState = GetKeyState(VK_NUMLOCK)

'my code here

If NumLockState <> GetKeyState(VK_NUMLOCK) Then
Application.SendKeys ("%{Numlock}"), True
End If


I am looking for a solution that will leave the numlock state in the same state that the code started on in both 32bit and 64bit installations of excel.

Thanks.