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

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


<(E-Mail Removed)> wrote in message
news:8f52605a-c365-4298-92b4-(E-Mail Removed)...
> 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

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)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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
>
>
> <(E-Mail Removed)> wrote in message
> news:8f52605a-c365-4298-92b4-(E-Mail Removed)...
> > 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
>

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" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>> 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
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:8f52605a-c365-4298-92b4-(E-Mail Removed)...
>> > 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
>>
>
>

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

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

On 25 Jul, 17:26, "Rob Bovey" <Rob_Bo...@msn.com> wrote:
> 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 Professionalshttp://www.appspro.com/
>
> * Take your Excel development skills to the next level.
> * Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm
>
> <joshuafanda...@dsl.pipex.com> wrote in message
>
> news:8f52605a-c365-4298-92b4-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -