Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlToolTip
I have a number of listboxes and comboboxes on an spreadsheet. I fairly sure you can use ControlTipText to pop up a comment type box using mousemove event when a control is on a userform, but I can't do this for those on my spreadsheet. Is there a way of doing this? -- Jimbo1 ------------------------------------------------------------------------ Jimbo1's Profile: http://www.excelforum.com/member.php...o&userid=30637 View this thread: http://www.excelforum.com/showthread...hreadid=526757 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlToolTip
To do this, put the following code in a standard code module (making it
available to the whole workbook). '------------- bas module ------------------------ Option Explicit Declare Function GetSystemMetrics Lib "user32" ( _ ByVal nIndex As Long) As Long Declare Function GetSysColor Lib "user32" ( _ ByVal nIndex As Long) As Long Public Function CreateToolTipLabel(objHostOLE As Object, _ sTTLText As String) As Boolean Dim objToolTipLbl As OLEObject Dim objOLE As OLEObject Const SM_CXSCREEN = 0 Const COLOR_INFOTEXT = 23 Const COLOR_INFOBK = 24 Const COLOR_WINDOWFRAME = 6 Application.ScreenUpdating = False For Each objOLE In ActiveSheet.OLEObjects If objOLE.Name = "TTL" Then objOLE.Delete Next objOLE 'create a label control... Set objToolTipLbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label .1") '...and format it to look as a ToolTipWindow With objToolTipLbl .Top = objHostOLE.Top + objHostOLE.Height - 10 .Left = objHostOLE.Left + objHostOLE.Width - 10 .Object.Caption = sTTLText .Object.Font.Size = 8 .Object.BackColor = GetSysColor(COLOR_INFOBK) .Object.BackStyle = 1 .Object.BorderColor = GetSysColor(COLOR_WINDOWFRAME) .Object.BorderStyle = 1 .Object.ForeColor = GetSysColor(COLOR_INFOTEXT) .Object.TextAlign = 1 .Object.AutoSize = False .Width = GetSystemMetrics(SM_CXSCREEN) .Object.AutoSize = True .Width = .Width + 2 .Height = .Height + 2 .Name = "TTL" End With DoEvents Application.ScreenUpdating = True 'delete the tooltip window after 5 secs Application.OnTime Now() + TimeValue("00:00:05"), "DeleteToolTipLabels" End Function Public Sub DeleteToolTipLabels() Dim objToolTipLbl As OLEObject For Each objToolTipLbl In ActiveSheet.OLEObjects If objToolTipLbl.Name = "TTL" Then objToolTipLbl.Delete Next objToolTipLbl End Sub '------------end of bas module ------------- Then in the code module for the sheet that has the control, add some mousedown event code. To get to this module, right-click on the sheet name tab, and selecw code (or double-click on the sheet name from within the VB IDE). Here is an example of how to call it, assuming that the command button is calle cmdTooltipTest Private Sub ComboBox1_MouseMove(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) Dim objTTL As OLEObject Dim fTTL As Boolean For Each objTTL In ActiveSheet.OLEObjects fTTL = objTTL.Name = "TTL" Next objTTL If Not fTTL Then CreateToolTipLabel ComboBox1, "ToolTip Label" End If End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbo1" wrote in message ... I have a number of listboxes and comboboxes on an spreadsheet. I fairly sure you can use ControlTipText to pop up a comment type box using mousemove event when a control is on a userform, but I can't do this for those on my spreadsheet. Is there a way of doing this? -- Jimbo1 ------------------------------------------------------------------------ Jimbo1's Profile: http://www.excelforum.com/member.php...o&userid=30637 View this thread: http://www.excelforum.com/showthread...hreadid=526757 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ControlToolTip
Spot on. cheers. Jim -- Jimbo1 ------------------------------------------------------------------------ Jimbo1's Profile: http://www.excelforum.com/member.php...o&userid=30637 View this thread: http://www.excelforum.com/showthread...hreadid=526757 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|