Change all label controltiptexts in userform

[joshuafandango]

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
there:

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

 

[Rob Bovey]

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

 

[Rick Rothstein \(MVP - VB\)]

I'm not 100% sure what you are asking here. First, we are talking about the
controls on a UserForm, right? Second, are you asking to change the
ControlTipText for Labels only? If so, is the condition that Labels
**without** a ControlTipText assigned to it should show its own Caption (and
those that do have a ControlTipText assign to it should continue to show
that)? If so, give this code a try...

Sub Change_Labels_UserForm()
Dim ctl As Control
For Each ctl In UserForm1.Controls
If TypeOf ctl Is MSForms.Label Then
If ctl.ControlTipText = "" Then ctl.ControlTipText = ctl.Caption
End If
Next
End Sub

Rick

 

[Peter T]

Rick, the OP is asking about programmatically "designing" a Userform
(actually all the userforms), such that when the code terminates the changes
to control properties persist. Unfortunately there's no VB6 equivalent
method.

Regards,
Peter T

 

[Rick Rothstein \(MVP - VB\)]

Thanks for clearing that up for me. As for not being able to do it in VB6...
while I'm still not 100% sure of what the OP is after, I would think
preserving changes should theoretically be able to be done by writing out
the changes to a file or the registry in some coded fashion and then reading
them back in when the UserForm is initialized.

Rick

 

[Peter T]

H i Rick,

Thanks for clearing that up for me.

I'm not sure I have !

As for not being able to do it in VB6... while I'm still not 100% sure of
what the OP is after,

The OP is looking to change his Labels' ControlTipText to read same as the
name of the caption, but only if the ControlTipText property is empty. He
wants to do this is a way that when the code terminates, the updated text
remains as the ControlTipText property. If he has a lot of labels it's
tedious to do them manually at design.

I would think preserving changes should theoretically be able to be done
by writing out the changes to a file or the registry in some coded fashion
and then reading them back in when the UserForm is initialized.

I take it you mean store property data somewhere, registry, text-file, or
cells on a hidden sheet, then update the control properties in the
Initialize event. That of course is possible; indeed for the OP's particular
purpose I guess similar could be done in each form's initialize event by
reading label captions pretty much as already doing. Perhaps there's a
reason the OP wants the properties set at design.

As for not being able to do it in VB6

With the VBA form's "Designer" you can create an entire form with controls
from scratch. I have found this to be particularly where large numbers of
controls are involved. Their names, types, properties etc can be designed
literally in table form on a spreadsheet. Run some code that reads the cell
data and convert into a new saveable form. AFAIK, the only way in VB6 to do
something similar is to write the *.frm file as text. I once made a start
but gave up!

Regards,
Peter T

 

[joshuafandango]

Thanks Rob,

I swear I tried that! Maybe a case of looking at something for too
long to be able to see the obvious?

Cheers,
JF