A VBA Question (Tooltip)

[a_k93]

Hi

How can I add Tool tip text to a text box which I have added to my Excel sheet?

Thnx in advance.

Regards
a_k93

[Hack]

Moved to Office Development

[RobDog888]

No easy way but you would want to use the MouseMove event to determine when the mouse is over the textbox.

I know there are a few good tooltip examples in the CodeBank but I have never tried using them on an Excel ccontrol.

Which textbox is it? A Forms or ActiveX Textbox?

[a_k93]

Hi there,

Thnx for your reply,

I am newbie for excel VBA. I have right click on the toolbar and selected Control Toolbox. which then gave me n nos of control. Then I have dragged and drop the controls on the excel sheet.

Can I ask u more thing.
I have around 100 textboxes and 15 combo box and 5 check box on a sheet and around 125 labels and likewise I have 5 sheets. Now I am on particular sheet I want to clear all the textboxes from that sheet.
Like in VB6 v write
Dim ctrl As Object
For Each ctrl In Me.Controls
Debug.Print ctrl.Name
Next
How do I do the same for Excel controls which I have in my all sheets.

Once again, thnx for your reply,

Regards,
a_k93

[RobDog888]

Check out the InlineShapes or OLEObjects collections.

[a_k93]

Hi there,

Thanx for u r help Rob.

I was looking for the code which will set the properties of all the controls placed on the sheet.
The code below does the same.
I have written the generic method in the module

Public Sub Clear_ctrl(strShetName As String)
Dim objWs
Dim objShape
Set objWs = Worksheets(strShetName)
For Each objShape In objWs.Shapes
If objShape.Type = msoOLEControlObject Then
If objShape.OLEFormat.ProgId = "Forms.CheckBox.1" Then
objWs.OLEObjects(objShape.Name).Object.Value = 0
End If
If objShape.OLEFormat.ProgId = "Forms.TextBox.1" Then
objWs.OLEObjects(objShape.Name).Object.Text = ""
End If
If objShape.OLEFormat.ProgId = "Forms.ComboBox.1" Then
objWs.OLEObjects(objShape.Name).Object.Text = ""
End If
If objShape.OLEFormat.ProgId = "Forms.OptionButton.1" Then
objWs.OLEObjects(objShape.Name).Object.Value = 1
End If

End If
Next

End Sub


Thanx for u r support.

Rob, can u elaborate little bit more on the Tooltip point, like how should I go about when I move the mouse over any textbox which needs to show the tooltip, do I need to call any api or anything like ....

Thanx
a_k93

[RobDog888]

Use the MouseMove event for each of the controls. It fires when the mouse moves over the control.