Artik
Active Member
- Joined
- Jun 5, 2012
- Messages
- 286
I use the 32-bit version of Office on a daily basis. For many years I have been using the following code to display the UserForm close to the clicked cell. And it works properly.
Recently, however, I have a need to use this code in the 64-bit version. It seems that after adjusting the API function declarations for the 64-bit version, the code should work the same as in the 32-bit version. Unfortunately, it doesn't.
I don't know much about Win API functions, but I understand roughly the operation of these two lines
The first line returns a handle to the window descendant of the "EXCEL6" class window (in this case, a programmatically edited cell for a while). This mechanism of operation I do not understand. How is the descendant, i.e. the edited cell, determined? The second line of code returns the dimensions and location of this cell (in pixels).
In the 64-bit version, this piece of code does not work properly. Left and Right always get a value of zero, while Top and Bottom get the same values (perhaps the height of the ribbon). I conclude that the dimensions of the programmatically edited cell were not read.
Could someone explain what the problem is? Alternatively, suggest another solution that works in the 64-bit version.
Best regards
Artik
VBA Code:
Option Explicit
Private Declare PtrSafe Function FindWindowExA Lib "User32" _
(ByVal Hwnd As LongPtr, _
ByVal HChildAfter As LongPtr, _
ByVal lpsz1 As String, _
ByVal lpsz2 As Any) As LongPtr
Private Declare PtrSafe Function GetWindowRect Lib "User32" _
(ByVal Hwnd As LongPtr, _
ByRef lpRect As RECT) As LongPtr
Private Declare PtrSafe Function SendInput Lib "User32" _
(ByVal nInputs As Long, _
ByRef pInputs As GENERAL_INPUT, _
ByVal cbSize As Long) As Long
Private Declare PtrSafe Sub RtlMoveMemory Lib "Kernel32" _
(ByRef pDst As Any, _
ByRef pSrc As Any, _
ByVal ByteLen As LongPtr)
Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type
Private Type KEYBDINPUT
wVk As Integer
wScan As Integer
dwFlags As Long
time As Long
dwExtraInfo As Long
End Type
Private Type GENERAL_INPUT
dwType As Long
dwData(23) As Byte
End Type
Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Range, Cancel As Boolean)
Dim Hwnd As LongPtr
Dim Rec As RECT
Dim pxWidth As Long
Dim Ratio As Double
Dim acc As IAccessible
Dim KInput As KEYBDINPUT
Dim GInput(3&) As GENERAL_INPUT
Dim b As Boolean
Dim i As Long
Cancel = True
For i = 0 To UBound(GInput)
GInput(i).dwType = 1&
Next
KInput.wVk = vbKeyF2
KInput.dwFlags = 0&
RtlMoveMemory GInput(0&).dwData(0&), KInput, Len(KInput)
KInput.dwFlags = 2&
RtlMoveMemory GInput(1&).dwData(0&), KInput, Len(KInput)
KInput.wVk = vbKeyEscape
KInput.dwFlags = 0&
RtlMoveMemory GInput(2&).dwData(0&), KInput, Len(KInput)
KInput.dwFlags = 2&
RtlMoveMemory GInput(3&).dwData(0&), KInput, Len(KInput)
With Application
b = .EditDirectlyInCell
.EditDirectlyInCell = True
End With
SendInput 4&, GInput(0&), Len(GInput(0&))
DoEvents
Application.EditDirectlyInCell = b
Hwnd = FindWindowExA(Application.Hwnd, 0&, "XLDESK", 0&)
Hwnd = FindWindowExA(Hwnd, 0&, "EXCEL6", 0&)
GetWindowRect Hwnd, Rec
Set acc = UserForm1
Set acc = acc.accParent
acc.accLocation 0&, 0&, pxWidth, 0&, 0&
With UserForm1
.StartUpPosition = 0&
Ratio = .Width / pxWidth
.Left = (Rec.Left - 1) * Ratio + Target(1).Width
.Top = (Rec.Top - 1) * Ratio + Target(1).Height
.Show 0&
End With
End Sub
I don't know much about Win API functions, but I understand roughly the operation of these two lines
VBA Code:
Hwnd = FindWindowExA(Hwnd, 0&, "EXCEL6", 0&)
GetWindowRect Hwnd, Rec
In the 64-bit version, this piece of code does not work properly. Left and Right always get a value of zero, while Top and Bottom get the same values (perhaps the height of the ribbon). I conclude that the dimensions of the programmatically edited cell were not read.
Could someone explain what the problem is? Alternatively, suggest another solution that works in the 64-bit version.
Best regards
Artik