vbKeyCapital

[jnf40]

I have a workbook that when opened a box pops up for the user to enter a
password. I would like to have a message box pop up if the CapsLock is on,
this way if they can enter the password correctly. As it is now if they type
in the password and they have the CapsLock on it will only give them viewing
rights. Is it possible to have Excel recognize when the CapsLock is on, if so
how would I put this into code?
Thanks in advance,
jnf40

 

[John Bundy]

Try this in the workbook open:
If IsKeyPressed(gksKeyboardCapsLock) = True Then MsgBox "Caps Lock Is On"
and put this in a module: (from 0'reilly)

Private Declare Function GetKeyState Lib "user32" _
(ByVal vKey As Long) As Integer

Private Const VK_SHIFT As Long = &H10
Private Const VK_CONTROL As Long = &H11
Private Const VK_MENU As Long = &H12
Private Const VK_CAPITAL = &H14
Private Const VK_NUMLOCK = &H90
Private Const VK_SCROLL = &H91

Public Enum GetKeyStateKeyboardCodes
gksKeyboardShift = VK_SHIFT
gksKeyboardCtrl = VK_CONTROL
gksKeyboardAlt = VK_MENU
gksKeyboardCapsLock = VK_CAPITAL
gksKeyboardNumLock = VK_NUMLOCK
gksKeyboardScrollLock = VK_SCROLL
End Enum

Public Function IsKeyPressed _
(ByVal lKey As GetKeyStateKeyboardCodes) As Boolean

Dim iResult As Integer

iResult = GetKeyState(lKey)

Select Case lKey
Case gksKeyboardCapsLock, gksKeyboardNumLock, _
gksKeyboardScrollLock

'For the three 'toggle' keys, the 1st bit says if it's
'on or off, so clear any other bits that might be set,
'using a binary AND
iResult = iResult And 1

Case Else
'For the other keys, the 16th bit says if it's down or
'up, so clear any other bits that might be set, using a
'binary AND
iResult = iResult And &H8000
End Select

IsKeyPressed = (iResult <> 0)

End Function

 

[Jim Thomlinson]

Give this a wirl...

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long)
As Integer
Private Const kCapital = 20
Private Const kNumlock = 144

Public Function CapsLock() As Boolean
CapsLock = KeyState(kCapital)
End Function

Public Function NumLock() As Boolean
NumLock = KeyState(kNumlock)
End Function

Private Function KeyState(lKey As Long) As Boolean
KeyState = CBool(GetKeyState(lKey))
End Function

Sub Test()
MsgBox CapsLock
MsgBox NumLock
End Sub

 

[Vergel Adriano]

you got answers for checking the status of the CapsLock key.. But just in
case, the reason you want to do that is because the password is in lowercase
and you don't want it to be case sensitive, then, you can use UCase (or
LCase) to put the password and user input in the same case:

If UCase(strThePassword) = UCase(strUserInputPassword) Then
'Correct password
End if

 

[Chip Pearson]

If UCase(strThePassword) = UCase(strUserInputPassword) Then

A more efficient way is to use StrComp. E.g.,

If StrComp(strThePassword, strUserInputPassword, vbTextCompare) = 0 Then
' passwords match
Else
' passwords don't match
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)

 

[Vergel Adriano]

Hi Chip,

I think I've heard before that StrComp was more efficient, but I personally
have not seen the difference.

Just for my own curiosity, is there a way such difference can be made
visible? For example, I've tried comparing the two methods with code like
the one below, but I seem to be getting the same results..

Sub UCasevsStrComp()
Dim String1 As String
Dim String2 As String
Dim dtStartTime As Date
Dim dtEndTime As Date
Dim dblElapsedTime As Double
Dim l As Long
Dim x As Long

String1 = "a StRiNG TO ComparE"
String2 = "A STRIng to coMParE"

dtStartTime = Now()
For l = 1 To 1000000
If UCase(String1) = UCase(String2) Then
x = l
End If
Next l
dtEndTime = Now()
dblElapsedTime = (dtEndTime - dtStartTime) * 86400 * 1000
Debug.Print Format(dblElapsedTime, "#0.00000000") & " milliseconds elapsed!"

dtStartTime = Now()
For l = 1 To 1000000
If StrComp(String1, String2, vbTextCompare) = 0 Then
x = l
End If
Next l
dtEndTime = Now()
dblElapsedTime = (dtEndTime - dtStartTime) * 86400 * 1000
Debug.Print Format(dblElapsedTime, "#0.00000000") & " milliseconds elapsed!"

End Sub