What is wrong with this VBA code: userForm get hand ico for pointer on hover

ammer

New Member
Joined
Feb 27, 2024
Messages
6
Office Version
  1. 2021
  2. 2019
  3. 2016
Platform
  1. Windows
Salutes,
GDI / GDIP / OpenGL, windows API, and I still find it hard tackling with. Search results for changing cursor icon to hand on hovering on a link_label didn't afford except this solution came up by jaafar-tribak .

I get this error from compiler :
Compiler Error
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with PtrSafe attribute.

1709088446926.png


● If someone could help converting it into 64-bit, with many thanks
The code is picked from this Thread on this Forum, right at the end of the thread (Userform Hand Mouse Icon when scroll over buttons)

--------------------------

VBA Code:
Option Explicit
 
Public WithEvents cmb As CommandButton

Private Type POINTAPI
    X As Long
    Y As Long
End Type
 
Private Type MSG
    hwnd As Long
    message As Long
    wParam As Long
    lParam As Long
    time As Long
    pt As POINTAPI
End Type
 
Private Declare Function PeekMessage Lib "user32" _
Alias "PeekMessageA" _
(ByRef lpMsg As MSG, ByVal hwnd As Long, _
ByVal wMsgFilterMin As Long, _
ByVal wMsgFilterMax As Long, _
ByVal wRemoveMsg As Long) As Long
 
Private Declare Function WaitMessage Lib "user32" _
() As Long
 
Private Declare Function PostMessage Lib "user32.dll" _
Alias "PostMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
 
Private Declare Function GetCursorPos Lib "user32.dll" _
(ByRef lpPoint As POINTAPI) As Long
 
Private Declare Function SetCursor Lib "user32" _
(ByVal hCursor As Long) As Long
 
Private Declare Function LoadCursor Lib "user32.dll" _
Alias "LoadCursorA" _
(ByVal hInstance As Long, _
ByVal lpCursorName As Long) As Long
 
Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function WindowFromPoint Lib "user32" _
(ByVal xPoint As Long, ByVal yPoint As Long) As Long

Private Const PM_NOREMOVE As Long = &H0
Private Const WM_SETCURSOR As Long = &H20

Private bMouseHooked As Boolean
Private bStop As Boolean
Private bCurOverButton As Boolean

Private lCur As Long
Private oFrm As UserForm

Public Sub ChangeCurOf(frm As UserForm, ByVal Button As CommandButton, ByVal Cur As Long)
    Dim tMsg As MSG
    Dim tPt As POINTAPI
    Dim lCurID As Long
    Dim hwnd As Long
    If bMouseHooked Then Exit Sub
    bMouseHooked = True
    bStop = False
    lCur = Cur
    Set oFrm = frm
    hwnd = _
    FindWindow(vbNullString, frm.Caption)
    If Not bCurOverButton Then Exit Sub
    Do
        GetCursorPos tPt
        If WindowFromPoint(tPt.X, tPt.Y) <> hwnd Then bStop = True
        SetCursor LoadCursor(0, Cur)
        WaitMessage
        If PeekMessage _
        (tMsg, hwnd, _
        WM_SETCURSOR, WM_SETCURSOR, PM_NOREMOVE) Then
            PostMessage hwnd, WM_SETCURSOR, 0, 0
        End If
        DoEvents
    Loop Until bStop
End Sub


Private Sub cmb_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    bStop = True
    bMouseHooked = False
    bCurOverButton = True
    Call ChangeCurOf(oFrm, cmb, lCur)
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
See one of the Similar Threads listing at the bottom of your thread. Or review this one
or this site for the whole enchilada
 
Upvote 0
Here is a much shorter vaersion of the code and which should work in both, x32 and x64.

File Download:
ButtonCursor_V2.xlsm




1- Class Module (CButtonCursors)
VBA Code:
Option Explicit
 
Public WithEvents cmb As CommandButton

#If VBA7 Then
    Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As LongPtr) As LongPtr
    Private Declare PtrSafe Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As LongPtr, ByVal lpszName As LongPtr, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
#Else
    Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
    Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpszName As Long, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
#End If
 
Private lCursor As Long

Private Sub Class_Initialize()
    lCursor = Evaluate(["Cursor_"])
    Names("Cursor_").Delete
End Sub

Private Sub cmb_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    #If Win64 Then
        Const NULL_PTR = 0^
    #Else
        Const NULL_PTR = 0&
    #End If
    Const IMAGE_CURSOR = &H2, LR_SHARED = &H8000
    Call SetCursor(LoadImage(NULL_PTR, lCursor, IMAGE_CURSOR, 0&, 0&, LR_SHARED))
End Sub



2- UserForm Code :
VBA Code:
Option Explicit

Private Enum CURSORS
    IDC_HAND = 32649&
    IDC_SIZEALL = 32646&
    IDC_SIZE = 32640&
    IDC_APPSTARTING = 32650&
    IDC_ICON = 32641&
    IDC_WAIT = 32514&
    IDC_IBEAM = 32513&
    IDC_CROSS = 32515&
    IDC_UPARROW = 32516&
    IDC_NO = 32648&
    IDC_HANDWRITING = 32631
End Enum

Private Sub UserForm_Initialize()
    Call SetCursor(CommandButton1, IDC_HAND)
    Call SetCursor(CommandButton2, IDC_NO)
    Call SetCursor(CommandButton3, IDC_SIZEALL)
    Call SetCursor(CommandButton4, IDC_HANDWRITING)
End Sub

Private Sub SetCursor(ByVal Button As CommandButton, ByVal Cursor As CURSORS)
    Static ar() As CButtonCursors
    Static i As Long
    Names.Add "Cursor_", Cursor, False
    ReDim Preserve ar(i)
    Set ar(i) = New CButtonCursors
    Set ar(i).cmb = Button
    i = i + 1
End Sub
 
Upvote 0
Here is a much shorter vaersion of the code and which should work in both, x32 and x64.

File Download:
ButtonCursor_V2.xlsm




1- Class Module (CButtonCursors)
VBA Code:
Option Explicit
 
Public WithEvents cmb As CommandButton

#If VBA7 Then
    Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As LongPtr) As LongPtr
    Private Declare PtrSafe Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As LongPtr, ByVal lpszName As LongPtr, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
#Else
    Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
    Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpszName As Long, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
#End If
 
Private lCursor As Long

Private Sub Class_Initialize()
    lCursor = Evaluate(["Cursor_"])
    Names("Cursor_").Delete
End Sub

Private Sub cmb_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    #If Win64 Then
        Const NULL_PTR = 0^
    #Else
        Const NULL_PTR = 0&
    #End If
    Const IMAGE_CURSOR = &H2, LR_SHARED = &H8000
    Call SetCursor(LoadImage(NULL_PTR, lCursor, IMAGE_CURSOR, 0&, 0&, LR_SHARED))
End Sub



2- UserForm Code :
VBA Code:
Option Explicit

Private Enum CURSORS
    IDC_HAND = 32649&
    IDC_SIZEALL = 32646&
    IDC_SIZE = 32640&
    IDC_APPSTARTING = 32650&
    IDC_ICON = 32641&
    IDC_WAIT = 32514&
    IDC_IBEAM = 32513&
    IDC_CROSS = 32515&
    IDC_UPARROW = 32516&
    IDC_NO = 32648&
    IDC_HANDWRITING = 32631
End Enum

Private Sub UserForm_Initialize()
    Call SetCursor(CommandButton1, IDC_HAND)
    Call SetCursor(CommandButton2, IDC_NO)
    Call SetCursor(CommandButton3, IDC_SIZEALL)
    Call SetCursor(CommandButton4, IDC_HANDWRITING)
End Sub

Private Sub SetCursor(ByVal Button As CommandButton, ByVal Cursor As CURSORS)
    Static ar() As CButtonCursors
    Static i As Long
    Names.Add "Cursor_", Cursor, False
    ReDim Preserve ar(i)
    Set ar(i) = New CButtonCursors
    Set ar(i).cmb = Button
    i = i + 1
End Sub
Thanks man much appreciated , I couldn't find a way to send you a message. I was hoping a label control could have such an effect, I use links in my userforms, each link in a separate label. I hope this would be possible.
★ I owe you so much, and I'd like to share my projs with you in the near future.
thank you @Jaafar Tribak

Tashakurat jaafar​

 
Upvote 0
@ammer
Yes. we can amend the class code and apply a similar technique to label controls so that they look and behave just like real Hyperlinks including changing the hyperlink text hover and visited colors and hand cursor.
Here is an example:

File Demo:
LabelMimickingHyperlink.xlsm






1- Class Module (CHyperlinkLabel)

VBA Code:
Option Explicit

#If Win64 Then
    Const NULL_PTR = 0^
#Else
    Const NULL_PTR = 0&
#End If

Private Type POINTAPI
    X As Long
    Y As Long
End Type
 
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As LongPtr, ByVal szURL As LongPtr, ByVal dwReserved As Long) As Long
    Private Declare PtrSafe Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As LongPtr, ByVal lpszName As LongPtr, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As Long, ByVal szURL As Long, ByVal dwReserved As Long) As Long
    Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpszName As Long, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
    Private Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
#End If

Private WithEvents LblEvent As MSForms.Label


Public Sub HookLabel(ByVal lbl As MSForms.Label)
    Const COLOR_HOTLIGHT = 26&
    lbl.BackStyle = fmBackStyleTransparent
    lbl.ForeColor = GetSysColor(COLOR_HOTLIGHT)
    Set LblEvent = lbl
End Sub

Private Sub LblEvent_Click()
    Const S_OK = 0&
    If IsValidURL(NULL_PTR, StrPtr(LblEvent.Caption), 0&) = S_OK Then
        LblEvent.ForeColor = &H800080
        ThisWorkbook.FollowHyperlink LblEvent.Caption
    End If
End Sub

Private Sub LblEvent_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  
    Const IMAGE_CURSOR = &H2, LR_SHARED = &H8000
    Const IDC_HAND = 32649&
    Const VT_BSTR = 8&
  
    Static bMouseHovering As Boolean
    Dim tCurPos As POINTAPI
    Dim oParentAcc  As IAccessible, vChild As Variant
    Dim lDataType As Long
 
    Call SetCursor(LoadImage(NULL_PTR, IDC_HAND, IMAGE_CURSOR, 0&, 0&, LR_SHARED))
  
    If bMouseHovering Then Exit Sub
 
    Set oParentAcc = LblEvent.Parent
    LblEvent.Font.Underline = True
    Do
        bMouseHovering = True
        Call GetCursorPos(tCurPos)
        On Error Resume Next
        vChild = oParentAcc.accHitTest(tCurPos.X, tCurPos.Y)
        Call CopyMemory(lDataType, vChild, 2&)
        If lDataType <> VT_BSTR Then
            Exit Do
        End If
        On Error GoTo 0
        DoEvents
    Loop
    LblEvent.Font.Underline = False
    bMouseHovering = False

End Sub


2- Code Usage Example in the UserForm
VBA Code:
Option Explicit

Private Sub UserForm_Initialize()
    Call MakeHyperlink(Label1)
    Call MakeHyperlink(Label2)
    Call MakeHyperlink(Label3)
End Sub

Private Sub MakeHyperlink(ByVal Label As MSForms.Label)
    Static arHyperLabel() As CHyperlinkLabel
    Static i As Long
    ReDim Preserve arHyperLabel(i)
    Set arHyperLabel(i) = New CHyperlinkLabel
    Call arHyperLabel(i).HookLabel(Label)
    i = i + 1&
End Sub
 
Upvote 0
Hi @ammer

I just noticed a stealth problem in the Class code posted above: If the user closes the form using the keyboard, the code execution won't break out of the temporary loop. This is an unlikely, albeit serious, scenario that should be catered for.

So please, ignore the previous Class code and use the following one to which I have incorporated the necessary changes.

The demo file in the link has been updated.


VBA Code:
Option Explicit

#If Win64 Then
    Const NULL_PTR = 0^
#Else
    Const NULL_PTR = 0&
#End If

Private Type POINTAPI
    X As Long
    Y As Long
End Type
 
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As LongPtr, ByVal szURL As LongPtr, ByVal dwReserved As Long) As Long
    Private Declare PtrSafe Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As LongPtr, ByVal lpszName As LongPtr, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As Long, ByVal szURL As Long, ByVal dwReserved As Long) As Long
    Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpszName As Long, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
    Private Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As Long) As Long
    Private Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Long
#End If

Private WithEvents LblEvent As MSForms.Label


Public Sub HookLabel(ByVal lbl As MSForms.Label)
    Const COLOR_HOTLIGHT = 26&
    lbl.BackStyle = fmBackStyleTransparent
    lbl.ForeColor = GetSysColor(COLOR_HOTLIGHT)
    Set LblEvent = lbl
End Sub

Private Sub LblEvent_Click()
    Const S_OK = 0&
    If IsValidURL(NULL_PTR, StrPtr(LblEvent.Caption), 0&) = S_OK Then
        LblEvent.ForeColor = &H800080
        ThisWorkbook.FollowHyperlink LblEvent.Caption
    End If
End Sub

Private Sub LblEvent_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
    Const IMAGE_CURSOR = &H2, LR_SHARED = &H8000
    Const IDC_HAND = 32649&
    Const VT_BSTR = 8&
 
    Static bMouseHovering As Boolean
    Dim tCurPos As POINTAPI
    Dim oParentAcc  As IAccessible, vChild As Variant
    Dim lDataType As Long, hwnd As Long
 
    Call SetCursor(LoadImage(NULL_PTR, IDC_HAND, IMAGE_CURSOR, 0&, 0&, LR_SHARED))
 
    If bMouseHovering Then Exit Sub
    
    Call IUnknown_GetWindow(GetUserForm(LblEvent), VarPtr(hwnd))
    Set oParentAcc = LblEvent.Parent
    LblEvent.Font.Underline = True
    Do While IsWindowVisible(hwnd)
        bMouseHovering = True
        Call GetCursorPos(tCurPos)
        On Error Resume Next
        vChild = oParentAcc.accHitTest(tCurPos.X, tCurPos.Y)
        Call CopyMemory(lDataType, vChild, 2&)
        If lDataType <> VT_BSTR Then
            Exit Do
        End If
        On Error GoTo 0
        DoEvents
    Loop
    LblEvent.Font.Underline = False
    bMouseHovering = False

End Sub

Private Function GetUserForm(ByVal Ctrl As MSForms.Control) As UserForm
    Dim oTmp As Object
    Set oTmp = Ctrl.Parent
    Do While TypeOf oTmp Is MSForms.Control
        Set oTmp = oTmp.Parent
    Loop
    Set GetUserForm = oTmp
End Function


The code in the UserForm module stays the same as in post#5.
 
Last edited:
Upvote 0
@ammer
Yes. we can amend the class code and apply a similar technique to label controls so that they look and behave just like real Hyperlinks including changing the hyperlink text hover and visited colors and hand cursor.
Here is an example:

File Demo:
LabelMimickingHyperlink.xlsm






1- Class Module (CHyperlinkLabel)

VBA Code:
Option Explicit

#If Win64 Then
    Const NULL_PTR = 0^
#Else
    Const NULL_PTR = 0&
#End If

Private Type POINTAPI
    X As Long
    Y As Long
End Type
 
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As LongPtr, ByVal szURL As LongPtr, ByVal dwReserved As Long) As Long
    Private Declare PtrSafe Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As LongPtr, ByVal lpszName As LongPtr, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As Long, ByVal szURL As Long, ByVal dwReserved As Long) As Long
    Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpszName As Long, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
    Private Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
#End If

Private WithEvents LblEvent As MSForms.Label


Public Sub HookLabel(ByVal lbl As MSForms.Label)
    Const COLOR_HOTLIGHT = 26&
    lbl.BackStyle = fmBackStyleTransparent
    lbl.ForeColor = GetSysColor(COLOR_HOTLIGHT)
    Set LblEvent = lbl
End Sub

Private Sub LblEvent_Click()
    Const S_OK = 0&
    If IsValidURL(NULL_PTR, StrPtr(LblEvent.Caption), 0&) = S_OK Then
        LblEvent.ForeColor = &H800080
        ThisWorkbook.FollowHyperlink LblEvent.Caption
    End If
End Sub

Private Sub LblEvent_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
    Const IMAGE_CURSOR = &H2, LR_SHARED = &H8000
    Const IDC_HAND = 32649&
    Const VT_BSTR = 8&
 
    Static bMouseHovering As Boolean
    Dim tCurPos As POINTAPI
    Dim oParentAcc  As IAccessible, vChild As Variant
    Dim lDataType As Long
 
    Call SetCursor(LoadImage(NULL_PTR, IDC_HAND, IMAGE_CURSOR, 0&, 0&, LR_SHARED))
 
    If bMouseHovering Then Exit Sub
 
    Set oParentAcc = LblEvent.Parent
    LblEvent.Font.Underline = True
    Do
        bMouseHovering = True
        Call GetCursorPos(tCurPos)
        On Error Resume Next
        vChild = oParentAcc.accHitTest(tCurPos.X, tCurPos.Y)
        Call CopyMemory(lDataType, vChild, 2&)
        If lDataType <> VT_BSTR Then
            Exit Do
        End If
        On Error GoTo 0
        DoEvents
    Loop
    LblEvent.Font.Underline = False
    bMouseHovering = False

End Sub


2- Code Usage Example in the UserForm
VBA Code:
Option Explicit

Private Sub UserForm_Initialize()
    Call MakeHyperlink(Label1)
    Call MakeHyperlink(Label2)
    Call MakeHyperlink(Label3)
End Sub

Private Sub MakeHyperlink(ByVal Label As MSForms.Label)
    Static arHyperLabel() As CHyperlinkLabel
    Static i As Long
    ReDim Preserve arHyperLabel(i)
    Set arHyperLabel(i) = New CHyperlinkLabel
    Call arHyperLabel(i).HookLabel(Label)
    i = i + 1&
End Sub
============
Maaan it looks like you're really deeply experienced in digging the outmost of API libs! And not just for 32-bit but also for 64-bit configurations too. This deep understanding is obviously clarified by the rhythm of the tidy clean code definitely, that made me --the one whom never tackled in such libs to-- put_in even some more additions to my likings!

By your ultimate contribution I can say I've got everything I needed for the porj final touches I’ve been building. And not just that but also it gave me a quick vision, & know-how to deal with & use API libs in just few moments.
● My background experience is in php, JavaScript, & websites databases, and excel came thereafter from necessity.

★ I’ll be in touch frequently, your share will never be forgotten. I’m really interested in sharing my work with you.

Thank you in advance.
Tashakurat @Jaafar Tribak
 
Upvote 0
Hi @ammer

I just noticed a stealth problem in the Class code posted above: If the user closes the form using the keyboard, the code execution won't break out of the temporary loop. This is an unlikely, albeit serious, scenario that should be catered for.

So please, ignore the previous Class code and use the following one to which I have incorporated the necessary changes.

The demo file in the link has been updated.


VBA Code:
Option Explicit

#If Win64 Then
    Const NULL_PTR = 0^
#Else
    Const NULL_PTR = 0&
#End If

Private Type POINTAPI
    X As Long
    Y As Long
End Type
 
#If VBA7 Then
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
    Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As LongPtr, ByVal szURL As LongPtr, ByVal dwReserved As Long) As Long
    Private Declare PtrSafe Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As LongPtr, ByVal lpszName As LongPtr, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare PtrSafe Function SetCursor Lib "user32" (ByVal hCursor As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As LongPtr) As Long
    Private Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
    Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
    Private Declare Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
    Private Declare Function IsValidURL Lib "urlmon.dll" (ByVal pBZ As Long, ByVal szURL As Long, ByVal dwReserved As Long) As Long
    Private Declare Function LoadImage Lib "user32" Alias "LoadImageA" (ByVal hInst As Long, ByVal lpszName As Long, ByVal lType As Long, ByVal cxWidth As Long, ByVal cyHeight As Long, ByVal fuLoad As Long) As Long
    Private Declare Function SetCursor Lib "user32" (ByVal hCursor As Long) As Long
    Private Declare Function GetSysColor Lib "user32" (ByVal nIndex As Long) As Long
    Private Declare Function IUnknown_GetWindow Lib "shlwapi" Alias "#172" (ByVal pIUnk As IUnknown, ByVal hUF As Long) As Long
    Private Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Long
#End If

Private WithEvents LblEvent As MSForms.Label


Public Sub HookLabel(ByVal lbl As MSForms.Label)
    Const COLOR_HOTLIGHT = 26&
    lbl.BackStyle = fmBackStyleTransparent
    lbl.ForeColor = GetSysColor(COLOR_HOTLIGHT)
    Set LblEvent = lbl
End Sub

Private Sub LblEvent_Click()
    Const S_OK = 0&
    If IsValidURL(NULL_PTR, StrPtr(LblEvent.Caption), 0&) = S_OK Then
        LblEvent.ForeColor = &H800080
        ThisWorkbook.FollowHyperlink LblEvent.Caption
    End If
End Sub

Private Sub LblEvent_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
 
    Const IMAGE_CURSOR = &H2, LR_SHARED = &H8000
    Const IDC_HAND = 32649&
    Const VT_BSTR = 8&
 
    Static bMouseHovering As Boolean
    Dim tCurPos As POINTAPI
    Dim oParentAcc  As IAccessible, vChild As Variant
    Dim lDataType As Long, hwnd As Long
 
    Call SetCursor(LoadImage(NULL_PTR, IDC_HAND, IMAGE_CURSOR, 0&, 0&, LR_SHARED))
 
    If bMouseHovering Then Exit Sub
   
    Call IUnknown_GetWindow(GetUserForm(LblEvent), VarPtr(hwnd))
    Set oParentAcc = LblEvent.Parent
    LblEvent.Font.Underline = True
    Do While IsWindowVisible(hwnd)
        bMouseHovering = True
        Call GetCursorPos(tCurPos)
        On Error Resume Next
        vChild = oParentAcc.accHitTest(tCurPos.X, tCurPos.Y)
        Call CopyMemory(lDataType, vChild, 2&)
        If lDataType <> VT_BSTR Then
            Exit Do
        End If
        On Error GoTo 0
        DoEvents
    Loop
    LblEvent.Font.Underline = False
    bMouseHovering = False

End Sub

Private Function GetUserForm(ByVal Ctrl As MSForms.Control) As UserForm
    Dim oTmp As Object
    Set oTmp = Ctrl.Parent
    Do While TypeOf oTmp Is MSForms.Control
        Set oTmp = oTmp.Parent
    Loop
    Set GetUserForm = oTmp
End Function


The code in the UserForm module stays the same as in post#5.
# # # #
Yeah I liked the description (Stealth) problem, most of excel people I’ve met personally were stuck, and really embarrassed (blushed faces) from these excel (stealth) trouble issues in-front of their dealers, and even enforced me to spend several months resolving such issues from my proj one after the other, to be fully authentic (( Sealth Proof)) doesn’t let any mistake for users that might commit even if they don’t know what excel is. Yet still though made me suspicious about the efficiency of excel in general in building account-statements applications. Honestly by time I became really hectic about such troubles ends up in total crash, and imagine if transactions committed are not saved !

The proj is built on long experience and extensive testing by users for a very long time. I think after all it would be a powerful application.

★ I hope adopting this styling won’t cause any trouble. Especially the (Stealth) ones.

★ I wish you luck, wish me that too, I’ll be in touch
Thank you in advance @Jaafar Tribak
 
Upvote 0
You guys do know that userform controls have a MouseIcon property? ;)
 
Upvote 0
# # # #
(( Sealth Proof)) doesn’t let any mistake for users that might commit even if they don’t know what excel is...
Yes. that's called 'defensive programming' .

I am gald I could be of help and thanks for the feedbak ammer.
 
Upvote 0

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top