Excel - Reading the keyboard in VBA

Asked By dwinma on 17-Oct-08 10:57 PM
Can someone show me how to code the conditional statement shown in the
comments below i.e. how to put the "x" in the cell only if the SHIFT ( or
ctrl or alt)key is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
RowPos = Target.Row
ColPos = 3
' If Shift Key selected then
'   Put an "x" in cell row = RowPos and Column = ColPos
ThisWorkbook.ActiveSheet.Cells(RowPos, ColPos) = "x"
'End If
End Sub

ShaneDevenshir replied on 18-Oct-08 02:33 AM

Is this code you got from somewhere or a guess on how it might look?  I
think it would be better is you explained exactly what you want to do and to
what range because if you put an X into a cell everytime you move the cursor
with the SHIFT ALT or CTRL key down you may get some unpleasent supprises.

Shane Devenshire
Peter T replied on 18-Oct-08 05:58 AM
For the reasons Shane mentioned I doubt you individual Shift, Ctrl or Alt
down will be of use for you. OTH, Ctrl+Shift might serve your needs

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

Function ShiftCtrl() As Boolean
If GetAsyncKeyState(vbKeyControl) Then
ShiftCtrl = GetAsyncKeyState(vbKeyShift)
End If

End Function

' the above could go in a normal module and called from other object modules

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RowPos As Long, ColPos As Long

If Target.Count = 1 Then
If ShiftCtrl Then
RowPos = Target.Row
ColPos = 3
Cells(RowPos, ColPos) = "x"
End If
End If
End Sub

No need to qualify the sheet in the sheet module unless you want to refer to
some other sheet.

If you need to know more than simply Ctrl+Shift I'm sure you can adapt the
above for your needs.

Peter T