PDA

View Full Version : Determining if 'End Mode' is on



Paul_Hossler
04-10-2012, 09:14 AM
I'm writing a special .OnKey for {Left} (plus others)

I'd like to determine if End Mode is on, because the selected cell will be different

There doesn't seem to be a status I can test, or at least that I can find

The OnKey sub will look something like this


Sub My_Left()
If ActiveSheet.ProtectContents Then
If EndMode Then '<<<<<<<<<<<<<<<<<<<<
ActiveCell.EntireRow.Cells(1, 2).Select
Else
ActiveCell.Offset(0, -1).Select
End If
Else
ActiveCell.EntireRow.Cells(1, 1).Select
End If
End Sub


Paul

Kenneth Hobs
04-10-2012, 09:22 AM
Have you tried GetKeyState?

http://www.cpearson.com/excel/KeyTest.aspx

Paul_Hossler
04-10-2012, 10:52 AM
I use Chip's GetKeyState all the time, but as far as I can tell it will only tell you if the Alt, Ctrl, or Shift key is down.

Excel has the End Mode 'state' where if you press [end] and then [down] it works like range.End(xlDown).Select

Paul

Kenneth Hobs
04-10-2012, 12:09 PM
This can be a bit wacky in the 2nd MsgBox. DoEvents can help a bit sometimes.

Public Const VK_END = &H23

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

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

Sub PressKey(theKey As Long)
keybd_event theKey, 0, 0, 0 'press key
keybd_event theKey, 0, &H2, 0 'release key
End Sub

Sub Test_IsEndModeOne()
Dim tf As Boolean
tf = IsEndModeOn
MsgBox tf

If tf = True Then PressKey VK_END
tf = IsEndModeOn
MsgBox tf
End Sub

Function IsEndModeOn() As Boolean
IsEndModeOn = CBool(GetKeyState(VK_END))
End Function

Paul_Hossler
04-10-2012, 03:16 PM
I took the Msgbox's out, just be be sure.

The subs below are the bare bones of what I'm trying to do

It uses your suggestion, but the results are a little wacky (to use the technical term)

Sometimes the correct cell is selected, sometimes not


Option Explicit
Public Const VK_END = &H23

Private Declare Sub Keybd_Event Lib "user32" _
(ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

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

Sub PressKey(theKey As Long)
Keybd_Event theKey, 0, 0, 0 'press key
Keybd_Event theKey, 0, &H2, 0 'release key
End Sub

Function IsEndModeOn() As Boolean
IsEndModeOn = CBool(GetKeyState(VK_END))
End Function
Sub init()
Application.OnKey "{Left}", "My_Left"
End Sub
Sub done()
Application.OnKey "{Left}"
End Sub

Sub My_Left()

'[End] followed by [left] go to Col B in active row
If IsEndModeOn Then
DoEvents
ActiveCell.EntireRow.Cells(1, 2).Select

'Just [left] go to Col A in active row
Else
DoEvents
ActiveCell.EntireRow.Cells(1, 1).Select
End If
End Sub


The actual application will be more elaborate than this, but it would be helpful to be able to differeniate between (for ex.) 'one cell left' and 'left to the first one with data' from the KB.

Paul