call a macro on key up state..

[omairhe]

Hey all,

I am wondering if it is possible for a macro to be called upon when a key state is Up. It can be done in a user Form but I would like to achieve that in a sheet without any userform. But if a value is being entered in a cell and/or the formula bar is in active mode, then bypass/avoid the said code.

If it is possible .

Thanks.

 

[Logit]

.
Your description is slightly confusing. Overall though, I believe the answer to your question is Yes.

Please provide more specific information and let's see what we can create. If it's possible, create a sample
workbook with range/s filled in to demonstrate what you are speaking of, then place that workbook on
a cloud site like DROPBOX.COM so it can be downloaded for review.

Or you can paste an image of the sheet in your post.

 

[omairhe]

Hello Logit,

I am sorry that my OP was confusing to you but I could try my best to explain this perhaps in a different way.

First, I want you to open a new excel sheet and I want you to insert the following code in the worksheet code =

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
Range("A1").Select
End Sub

Now I want you to use the keyboard's arrow keys and you will notice that you will be unable to move away from cell A1. But now you use your mouse, first click on any other cell without releasing the click. You will notice that you have achieved to bypass or cheat the code. But as soon as you lift the finger off of your mouse button the code will take us back to A1. In a nut shell I want the same thing when I use the arrow keys. I want to be able to run through multiple cells as long as I don't release my finger off from the arrow key.
Thank you for your patience and help.

 

[Logit]

.
There would be an issue with detecting the ARROW KEYS 'key up' state.

Say you press the right arrow key. The cursor moves from cell to cell until you release the arrow key. Then it auto-returns to A1.
While the cursor is moving from cell to cell, you cannot enter anything into any of the cells. Once you let up, the cursor jumps to A1.

Are you saying you want the macro that forces the cursor to A1 to be suspended while you are using the arrow keys ? If so, I suspect
a better approach would then use a completely different key to activate the macro again.

Oftentimes you can receive a faster answer to your question if we understand what it is you intend to do with this. What is it that your project
is designed to do / accomplish and how ?

 

[omairhe]

.

Oftentimes you can receive a faster answer to your question if we understand what it is you intend to do with this. What is it that your project
is designed to do / accomplish and how ?

I am glad for your keen interest in solving my problem. I have a copy of someone else's project which I plan to implement on my own workbook. Problem is when I try to run on my workbook which is massive it tends to slow things up a bit and I feel the laptop is under lot of stress. I am sending the dropbox link for the said file.

https://www.dropbox.com/s/db7dlon4s0aigbm/Highlight-the-Active-Row-and-Column.xlsm?dl=0

In this sheet you will see conditional formatting rules that will highlight selective cells triggered by a sheet change event code (eg. Application.Calculate)

The above will work great but when I go down on a big list like 500 rows then performance will get slow. So I was wondering that while going down with using arrow key or any other key perhaps it will stop processing the code until the key is finally released. Hence, the last stop will be highlighted, leaving all the cells in between untouched, thus not making computer slow. I know its asking a lot but I'd appreciate any help on the subject.

Thanks again for your time and effort.

 

[Logit]

.
The download is approx. 22kb in size.

How large is your main workbook that runs slow ?

 

[omairhe]

.
The download is approx. 22kb in size.

How large is your main workbook that runs slow ?

around 2.38 Mb.

 

[Logit]

.
Usually when a workbook is slow to respond when using the various options provided within the workbook ... it appears more often than not
the cause is from the coding.

Using Loops or too many Loops.

Not turning off certain actions while the code does its thing, then turning those actions back on afterwards.

And other things ...

I recommend you post your code only so others can look at it as well to determine if the code might be optimized prior
to creating work arounds with additional code

I'm sending you a private message.

 

[omairhe]

ok Thanks.

 

[Jaafar Tribak]

@ omairhe

I have just seen this and gave it a shot .

Workbook example

The following code raises a pseudo-event only upon releasing any navigation key such as Up, Down, Enter, Tab keys etc ...
The code also conviniently aborts the native Worksheet_SelectionChange event when a selection is performed with the mouse.

Despite using a windows timer, the code should be stable and safe.

1- Put this code in a Standard Module :

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If


Public Sub StartWatching()
    SetTimer Application.hwnd, 0, 0, AddressOf WatchKeyState
End Sub


Private Sub WatchKeyState()

    Static vKey As Variant
    Dim vKeysArray As Variant, vKeysNames As Variant, i As Integer
    
    vKeysArray = Array(vbKeyDown, vbKeyUp, vbKeyLeft, vbKeyRight, vbKeyTab, vbKeyReturn, vbKeyLButton)
    vKeysNames = Array("Down", "Up", "Left", "Right", "Tab", "Return", "MouseClick")
    
    For i = 0 To UBound(vKeysArray)
        If GetAsyncKeyState(vKeysArray(i)) Then vKey = vKeysArray(i): Exit For
    Next i
    
    If vKey = vbKeyLButton Then KillTimer Application.hwnd, 0: Exit Sub
    
    On Error Resume Next
        vKey = vKeysNames(i)
    On Error GoTo 0
    
    If NavigationKeyStateUp Then
        KillTimer Application.hwnd, 0
        Call OnKeyUpPseudoEvent(Selection, CStr(vKey))
    End If

End Sub

Private Property Get NavigationKeyStateUp() As Boolean
    NavigationKeyStateUp = GetAsyncKeyState(vbKeyDown) + GetAsyncKeyState(vbKeyUp) _
    + GetAsyncKeyState(vbKeyLeft) + GetAsyncKeyState(vbKeyRight) + GetAsyncKeyState(vbKeyTab) = 0
End Property


[B][COLOR=#008000]'=================================================================================
'                                 PSEUDO-EVENT
'=================================================================================[/COLOR][/B]
Private Sub OnKeyUpPseudoEvent(ByVal Target As Range, ByVal vKey As String)
    MsgBox "You Relased the '" & vKey & "' Key" & vbNewLine & "At cell : '" & Target.Address & "'"
End Sub
[B][COLOR=#008000]'==================================================================================[/COLOR][/B]

2- This goes in the worksheet Module :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call StartWatching
End Sub