Hi JF,

You pretty much had it correct already, and the slight modification of
your code shown below works for me. Note that you're only changing the
ControlTipText if there isn't an entry already, so this may be confusing the
results.

Sub Change_Labels_UserForm()
Dim oVBComp As Object
Dim ctl As Control
On Error Resume Next
For Each oVBComp In ThisWorkbook.VBProject.VBComponents
If oVBComp.Type = 3 Then
For Each ctl In oVBComp.Designer.Controls
If TypeName(ctl) = "Label" And ctl.ControlTipText = "" Then
ctl.ControlTipText = ctl.Caption
End If
Next ctl
End If
Next oVBComp
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

wrote in message
...
Hi guys,

This is driving me insane!!!

I'm trying to have the text of all of the controltiptext properties on
a userform to be the same as the caption property of the label
(there's loads of labels).

It seemed like it should have been easy, but after much trial and
error I've managed to come up with the following (which I don't quite
follow) and feels so close, but I'm stumped as to get the caption in
the

Sub Change_Labels_UserForm()
Dim oVBProj, oVBComp As Object
Dim ctl As Control

Set oVBProj = ThisWorkbook.VBProject

On Error Resume Next
For Each oVBComp In oVBProj.VBComponents
If oVBComp.Type = 3 Then
For Each ctl In oVBComp.Designer.Controls
If Left(ctl.Name, 5) = "Label" and ctl.ControlTipText = ""
Then ctl.ControlTipText = 'caption name
Next
End If
Next
End Sub

Any ideas?

Cheers,
JF