Function GetAsyncKeyState Lib "user32.dll" .. Stops Working

[markw1230]

Hello All.

I have an excel workbook with a bunch of sheets. I am using the Function GetAsyncKeyState Lib "user32.dll" ... and it works fine till I Freeze or Unfreeze a sheet. The other sheets still work and work till I Unfreeze the sheet. If I close the workbook and reopen it function works till I Freeze/Unfreeze the sheet.

Any ideas..

Thank you,
Mark

[romperstomper]

What exactly do you mean by "stops working", and what do you mean by freezing a worksheet (freeze panes?)?

[markw1230]

Rory,

I am using "user32.dll" to check for a mouse click that works till I freeze or unfreeze a pane in the workbook. If I close the workbook and reopen it it works again. I have been searching high and low and can not find a fix. Below if a peak of the code I am uning.

Module1:

Option Explicit

Public Declare PtrSafe Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey As Long) As Integer


ThisWorkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim vLeftButton As Boolean

vLeftButton = GetAsyncKeyState(VK_LBUTTON) <> 0


Thank you,
Mark

[romperstomper]

OK and what precisely is happening with the code now? In what way is it not working after you freeze panes?

[pike]

Should that be with no PtrSafe in 32

Please Login or Register  to view this content.

and
and 64 something like

Please Login or Register  to view this content.

[romperstomper]

Hi Pike!

In a word, no. The declaration is correct as it is. (there's no such thing as IntPtr anyway)

[markw1230]

OK and what precisely is happening with the code now? In what way is it not working after you freeze panes?

Rorya,

GetAsyncKeyState(VK_LBUTTON) always returns "0" after I freeze or unfreeze a pane.

[Marc L]

Hi,

as this is very not an Excel feature, maybe more chance on a Windows forum …

Anyway try GetKeyState rather than GetAsyncKeyState.

[markw1230]

Hi,

as this is very not an Excel feature, maybe more chance on a Windows forum …

Anyway try GetKeyState rather than GetAsyncKeyState.

I just gave the above a try and if catches every other left mouse key till I freeze or unfreeze the pan, then stops working again.

Thanks, Mark

[Marc L]

So no other option than to not freeze / unfreeze any pan …

If you can test on a previous Excel version, different than 365, just to be sure it's a normal behaviour and not specific to 365.

[markw1230]

Can someone please try my stripped down sample code below and let me know if you get the same results as I do. I have been trying to get this to work and can not find the problem.
After setting up the code just click anywhere on the sheet to get started.

vGetAsuncKeyState and vGetkeyState should be True on every left mouse click.

Thank you for your help. Mark

Create Module1 and use code below:
-------------------------------------------------------------------------------------------------------
Option Explicit

Public Declare PtrSafe Function GetAsyncKeyState Lib "user32.dll" (ByVal vKey As Long) As Integer

Public Declare PtrSafe Function GetKeyState Lib "user32.dll" (ByVal vKey As Long) As Integer

Public Const VK_LBUTTON = &H1
Public Const VK_RBUTTON = &H2
--------------------------------------------------------------------------------------------------------

Place in ThisWorkbook and use code below:
--------------------------------------------------------------------------------------------------------
Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim vGetAsyncKeyState As Boolean
Dim vGetKeyState As Boolean

vGetAsyncKeyState = GetAsyncKeyState(VK_LBUTTON) <> 0
vGetKeyState = GetKeyState(VK_LBUTTON) <> 0

Range("A1").Value = "vGetAsyncKeyState = " & vGetAsyncKeyState
Range("A2").Value = "vGetKeyState = " & vGetKeyState

If IsEmpty(Range("A4")) Then ' Set up cells for display
Range("A4").Value = "Click to freeze pane"
Range("A4").Font.Underline = True
Columns("A:A").ColumnWidth = 25
Columns("B:B").ColumnWidth = 60
Range("B1").Value = "<-- Should be True on every left mouse click on a different cell."
Range("B2").Value = "<-- Should be True on every left mouse click on a different cell."
Range("B7").Value = "vGetAsyncKeyState works till you freeze the pane."
Range("B8").Value = "vGetKeyState works on every other mouse click."
End If

If Target.Row = "4" And Target.Column = "1" And Range("A4").Value = "Click to freeze pane" Then 'A1

Range("A4").Value = "Click to unfreeze pane"
Range("A5").Select
ActiveWindow.FreezePanes = True

ElseIf Target.Row = "4" And Target.Column = "1" And Range("A4").Value = "Click to unfreeze pane" Then 'A1

Range("A4").Value = "Click to freeze pane"
ActiveWindow.FreezePanes = False

End If

End Sub
-----------------------------------------------------------------------------------------------------------------

[romperstomper]

Yes, same result here (also 64 bit 365). It works on every other click once the panes are frozen

[pike]

Hey Rory!!
I was looking at the OP using the syntax

Please Login or Register  to view this content.

in 32bit. Isn't PtrSafe a 64bit attribute?
Yes you're right the other declarations aren't integrals , pointers or handles so don't need to be LongLong or longptr in 64bit , I find it hard to decipher which to change to and which ones to leave .. yes i did make intptr up on the fly...

[romperstomper]

PtrSafe was introduced with VBA7 and works regardless of bitness. As long as you don't need to worry about Excel 2007 or earlier, PtrSafe is fine; if you do, then you need conditional compilation for #If VBA7 then as well

[pike]

Know the op's pain ... crashed the application a few times with API's