Show all ControlTipText's in a UserForm on pressing 'Alt'

Question

I'm trying to add ControlTipText's to all MSForms.Control in all Userforms, that can be displayed.

Once all of them are Added, I want to Show all ControlTipText's on pressing 'Alt', so I can easily edit the Controltips on the Excel-Sheet.

Taking Baby-Steps, I first 'tried' to instantly make the ControlTip Visible when adding the Value to it.

What I have now is:

Dim tips As Worksheet
Set tips = Worksheets("CONTROLTIPS")

Dim i As Integer
Dim ctrl As MSForms.Control

i = 0
For Each ctrl In uf.Controls        
    ctrl.ControlTipText = tips.Cells(i + 3, 2).Value        
   ' ctrl .... ("TIPTEXT").Visible = True ?!?        
    i = i + 1        
Next ctrl

Answer 1

There is no direct way to show the tooltip of a control. The only way is to simulate the mouse hover using APIs. Here is a very basic example. Feel free to amend it to suit your needs.

Prep:

1. Create a blank userform
2. Place a commandbutton on the userform and set it's control tip text to whatever you want.

Logic:

When the ALT key is pressed, move the mouse over the relevant control thereby triggering the control tip text

Code

Paste this code in the userform

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Declare Function ClientToScreen Lib "user32" _
(ByVal hwnd As Long, lpPoint As POINTAPI) As Long

Private Declare Function SetCursorPos Lib "user32" _
(ByVal X As Long, ByVal Y As Long) As Long

Private Const Xindex = 88
Private Const Yindex = 90

Private Type POINTAPI
    X As Long
    Y As Long
End Type

'~~> Trap the Alt key in the keydown eveent
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = 18 Then MoveMouseOnTopOf Me, CommandButton1
End Sub

'~~> Simulate mouse hover
Public Sub MoveMouseOnTopOf(frm As Object, ctl As Object)
    Dim P As POINTAPI
    Dim usrfrmHwnd As Long
    Dim hDC As Long
    Dim X As Double, Y As Double

    hDC = GetDC(0)
    X = 72 / GetDeviceCaps(hDC, Xindex)
    Y = 72 / GetDeviceCaps(hDC, Yindex)
    ReleaseDC 0, hDC

    P.X = (ctl.Left + (ctl.Width \ 2)) / X
    P.Y = (ctl.Top + (ctl.Height \ 2)) / Y

    usrfrmHwnd = FindWindow(vbNullString, frm.Caption)
    ClientToScreen usrfrmHwnd, P

    SetCursorPos P.X, P.Y
End Sub

You can read and understand about the APIs used above in the AllAPI site.