Shape Names on User Form

[BillCPA]

I have created a user form that will be used to display the names of about 50
people and certain information about them (all 50 at once). Each person's
name will be on a label shape, and there will be 2-3 other labels and 2-3
text boxes for each person.

I want to have my own names for the labels and text boxes - say,
DataFormNamexxLbl for the label that will have the person's name. The first
person listed will be xx=1, next xx=2, etc. Same for the remaining labels
and text boxes.

Is there any way to set the Name parameter for all the DataFormNamexxLbl
shapes besides tediously going to each one and typing in the name I want? I
realize I can cut and paste and just have to change the xx part. But it sure
would be nice if there was a way to quickly set all the names?

Anyone have any ideas?

 

[Jim Cone]

Bill,
Shapes are used on sheets, Controls are used on UserForms.
On a userform you can iterate thru the controls collection...

For each ctrl in UserForm1.Controls
ctrl.Name = "Sludge"
Next
--
Jim Cone
Portland, Oregon USA



"BillCPA" <Bill @ UAMS>
wrote in message
I have created a user form that will be used to display the names of about 50
people and certain information about them (all 50 at once). Each person's
name will be on a label shape, and there will be 2-3 other labels and 2-3
text boxes for each person.
I want to have my own names for the labels and text boxes - say,
DataFormNamexxLbl for the label that will have the person's name. The first
person listed will be xx=1, next xx=2, etc. Same for the remaining labels
and text boxes.
Is there any way to set the Name parameter for all the DataFormNamexxLbl
shapes besides tediously going to each one and typing in the name I want? I
realize I can cut and paste and just have to change the xx part. But it sure
would be nice if there was a way to quickly set all the names?

Anyone have any ideas?

 

[Peter T]

You could add your controls at runtime, sized and positioned to suit.
You could then refer to your controls by index rather than by name.

Regards,
Peter T

 

[BillCPA]

When I try that, it tells me I cannot set the Name property at runtime. Is
there any way around that?

 

[Dave Peterson]

I'm not sure how you know which controls get renamed to what names, but maybe
this will help you get started.

Option Explicit
Sub Change_Labels_UserForm()
Dim oVBComp As Object
Dim ctl As Control
Dim lCtr As Long

lCtr = 0

Set oVBComp = ThisWorkbook.VBProject.vbcomponents("Userform1")
For Each ctl In oVBComp.Designer.Controls
If TypeOf ctl Is MSForms.Label Then
lCtr = lCtr + 1
ctl.Name = "DataFormName" & Format(lCtr, "00") & "Lbl"
End If
Next ctl
End Sub

 

[Dave Peterson]

I'm not sure if this will help you (maybe if you have multiple userforms to
update), but here's a post from Rob Bovey that I kept (and the basis for my
previous response):

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

The oVBComp.Type = 3 line means that it'll only process UserForms.

 

[BillCPA]

This is awesome. I've needed (wanted) something like this for years.

If I have 50 controls I want on a form that need to be numbered sequentially
(FormControlnnCkBx), I can just create a separate form, create one CheckBox,
copy and paste 'til I get 50, run the macro to name them (nn=01 to 50), and
copy them to the form I want them on.

Thanks so much!

--
Bill @ UAMS

I'm not sure if this will help you (maybe if you have multiple userforms to
update), but here's a post from Rob Bovey that I kept (and the basis for my
previous response):

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

The oVBComp.Type = 3 line means that it'll only process UserForms.