I have an user form that has 20 check boxes. I have duplicated it
several times by copying it in from another worksheet. I now need to
change the name of each check box in the new UserForms and the
controlsource for each. Is there a way to do this throuhg vba code.

For example; I have UserForm1 and it contains 20 check boxes named:

ChkBx_uf1_1...ChkBx_uf1_20

and the controlsource for each is a range:

ChkBxA_Link1...ChkBxA_Link20.

For each new User Form I have created (copied) I need to change the
names of the check boxes and the controlsources. So, UserForm2 would
have ChkBx_uf2_1...ChkBx_uf2_20 and the controlsources would be
ChkBx_uf2_Link1...ChkBx_uf2_20.

I've tried several approaches, but am getting nowhere.

Any help greatly appreciated.

this is a basic loop-thru code for a userform:

Dim oControl As Control
For Each oControl In Me.Controls
If TypeOf oControl Is msforms.CheckBox Then
oControl.Value = True
End If
Next oControl

if you put it in the userform_initialize sub (only for the first time;
take it out after that) you could ammend it & for each one do:

oControl.Name=" whatever"
oControl.ControlSource="whatever"

this is not tested.
hth
susan



On Mar 5, 1:06 pm, "hdf" <perfi...@gmail.com> wrote:
> I have an user form that has 20 check boxes. I have duplicated it
> several times by copying it in from another worksheet. I now need to
> change the name of each check box in the new UserForms and the
> controlsource for each. Is there a way to do this throuhg vba code.
>
> For example; I have UserForm1 and it contains 20 check boxes named:
>
> ChkBx_uf1_1...ChkBx_uf1_20
>
> and the controlsource for each is a range:
>
> ChkBxA_Link1...ChkBxA_Link20.
>
> For each new User Form I have created (copied) I need to change the
> names of the check boxes and the controlsources. So, UserForm2 would
> have ChkBx_uf2_1...ChkBx_uf2_20 and the controlsources would be
> ChkBx_uf2_Link1...ChkBx_uf2_20.
>
> I've tried several approaches, but am getting nowhere.
>
> Any help greatly appreciated.

Why would you not just set these as required in design mode?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"hdf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have an user form that has 20 check boxes. I have duplicated it
> several times by copying it in from another worksheet. I now need to
> change the name of each check box in the new UserForms and the
> controlsource for each. Is there a way to do this throuhg vba code.
>
> For example; I have UserForm1 and it contains 20 check boxes named:
>
> ChkBx_uf1_1...ChkBx_uf1_20
>
> and the controlsource for each is a range:
>
> ChkBxA_Link1...ChkBxA_Link20.
>
> For each new User Form I have created (copied) I need to change the
> names of the check boxes and the controlsources. So, UserForm2 would
> have ChkBx_uf2_1...ChkBx_uf2_20 and the controlsources would be
> ChkBx_uf2_Link1...ChkBx_uf2_20.
>
> I've tried several approaches, but am getting nowhere.
>
> Any help greatly appreciated.
>

On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> Why would you not just set these as required in design mode?
>
> --
> ---
> HTH
>
> Bob
>

I'm trying to avoid manually having to write the name and range name
in controlsource over and over 80 userforms x 20 chkboxes per form x 2
changes per userform.

By creating code I can simply create one set of instructions for the
first UserForm, use it once, then do a search and replace in the code
for the number (e.g. search for "Uf1" in the names called
"Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
the code again for the next UserForm and do this for each new
UserForm.

This would speed up the work a hundred fold, plus it avoids or limits
the chances for spelling errors.

The problem is that just changing the names isn't preserved, you need to do
it via Designer.

Something like this

Dim oVBProj As Object
Dim oVBComp As Object
Dim ctl As Control
Dim i As Long

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 TypeName(ctl) = "CheckBox" Then
i = i + 1
ctl.Name = "ChkBx_" & oVBComp.codemodule.Name & "_" & i
ctl.ControlSource = "=A1" '"=ChkBx_" &
oVBComp.codemodule.Name & "_Link" & i
End If
Next
End If
Next


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"hdf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> Why would you not just set these as required in design mode?
>>
>> --
>> ---
>> HTH
>>
>> Bob
>>
>
> I'm trying to avoid manually having to write the name and range name
> in controlsource over and over 80 userforms x 20 chkboxes per form x 2
> changes per userform.
>
> By creating code I can simply create one set of instructions for the
> first UserForm, use it once, then do a search and replace in the code
> for the number (e.g. search for "Uf1" in the names called
> "Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
> the code again for the next UserForm and do this for each new
> UserForm.
>
> This would speed up the work a hundred fold, plus it avoids or limits
> the chances for spelling errors.
>

On Mar 5, 8:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> The problem is that just changing the names isn't preserved, you need to do
> it via Designer.
>
> Something like this
>
> Dim oVBProj As Object
> Dim oVBComp As Object
> Dim ctl As Control
> Dim i As Long
>
> 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 TypeName(ctl) = "CheckBox" Then
> i = i + 1
> ctl.Name = "ChkBx_" & oVBComp.codemodule.Name & "_" & i
> ctl.ControlSource = "=A1" '"=ChkBx_" &
> oVBComp.codemodule.Name & "_Link" & i
> End If
> Next
> End If
> Next
>
> --
> ---
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "hdf" <perfi...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> >> Why would you not just set these as required in design mode?
>
> >> --
> >> ---
> >> HTH
>
> >> Bob
>
> > I'm trying to avoid manually having to write the name and range name
> > in controlsource over and over 80 userforms x 20 chkboxes per form x 2
> > changes per userform.
>
> > By creating code I can simply create one set of instructions for the
> > first UserForm, use it once, then do a search and replace in the code
> > for the number (e.g. search for "Uf1" in the names called
> > "Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
> > the code again for the next UserForm and do this for each new
> > UserForm.
>
> > This would speed up the work a hundred fold, plus it avoids or limits
> > the chances for spelling errors.

Bob, thank you for your response. I don't really know what Designer
is and I'm afraid that what you suggest is way over my head and I
think it would be more prudent to do it the old fashion way.

Thanks none the less.

Hector

i think (not sure) that bob's saying that while my approach will/might
work, the changes won't be saved............... (i'm going to test
this myself).
by "designer" mode i believe he just means to have the VBA editor open
& manually change all the names in the properties box.
susan

On Mar 5, 2:52 pm, "hdf" <perfi...@gmail.com> wrote:
> On Mar 5, 8:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>
>
>
>
>
> > The problem is that just changing the names isn't preserved, you need to do
> > it via Designer.
>
> > Something like this
>
> > Dim oVBProj As Object
> > Dim oVBComp As Object
> > Dim ctl As Control
> > Dim i As Long
>
> > 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 TypeName(ctl) = "CheckBox" Then
> > i = i + 1
> > ctl.Name = "ChkBx_" & oVBComp.codemodule.Name & "_" & i
> > ctl.ControlSource = "=A1" '"=ChkBx_" &
> > oVBComp.codemodule.Name & "_Link" & i
> > End If
> > Next
> > End If
> > Next
>
> > --
> > ---
> > HTH
>
> > Bob
>
> > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > "hdf" <perfi...@gmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > >> Why would you not just set these as required in design mode?
>
> > >> --
> > >> ---
> > >> HTH
>
> > >> Bob
>
> > > I'm trying to avoid manually having to write the name and range name
> > > in controlsource over and over 80 userforms x 20 chkboxes per form x 2
> > > changes per userform.
>
> > > By creating code I can simply create one set of instructions for the
> > > first UserForm, use it once, then do a search and replace in the code
> > > for the number (e.g. search for "Uf1" in the names called
> > > "Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
> > > the code again for the next UserForm and do this for each new
> > > UserForm.
>
> > > This would speed up the work a hundred fold, plus it avoids or limits
> > > the chances for spelling errors.
>
> Bob, thank you for your response. I don't really know what Designer
> is and I'm afraid that what you suggest is way over my head and I
> think it would be more prudent to do it the old fashion way.
>
> Thanks none the less.
>
> Hector- Hide quoted text -
>
> - Show quoted text -

well, i tried this code:

Option Explicit

Sub userform_initialize()

Dim oControl As Control
Dim StartNumber As Long

For Each oControl In Me.Controls
If TypeOf oControl Is msforms.CheckBox Then
StartNumber = 1
oControl.Name = "Userform2_Checkbox" & StartNumber
StartNumber = StartNumber + 1
End If
Next oControl

End Sub

& i get an error saying: " Run-time error '382': Could not set the
Name property. Can not set property at runtime."
so i guess that's why he's saying you can't do it.
susan


On Mar 5, 3:09 pm, "Susan" <bogenex...@aol.com> wrote:
> i think (not sure) that bob's saying that while my approach will/might
> work, the changes won't be saved............... (i'm going to test
> this myself).
> by "designer" mode i believe he just means to have the VBA editor open
> & manually change all the names in the properties box.
> susan
>
> On Mar 5, 2:52 pm, "hdf" <perfi...@gmail.com> wrote:
>
>
>
> > On Mar 5, 8:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > The problem is that just changing the names isn't preserved, you need to do
> > > it via Designer.
>
> > > Something like this
>
> > > Dim oVBProj As Object
> > > Dim oVBComp As Object
> > > Dim ctl As Control
> > > Dim i As Long
>
> > > 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 TypeName(ctl) = "CheckBox" Then
> > > i = i + 1
> > > ctl.Name = "ChkBx_" & oVBComp.codemodule.Name & "_" & i
> > > ctl.ControlSource = "=A1" '"=ChkBx_" &
> > > oVBComp.codemodule.Name & "_Link" & i
> > > End If
> > > Next
> > > End If
> > > Next
>
> > > --
> > > ---
> > > HTH
>
> > > Bob
>
> > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > > "hdf" <perfi...@gmail.com> wrote in message
>
> > >news:(E-Mail Removed)...
>
> > > > On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > >> Why would you not just set these as required in design mode?
>
> > > >> --
> > > >> ---
> > > >> HTH
>
> > > >> Bob
>
> > > > I'm trying to avoid manually having to write the name and range name
> > > > in controlsource over and over 80 userforms x 20 chkboxes per form x 2
> > > > changes per userform.
>
> > > > By creating code I can simply create one set of instructions for the
> > > > first UserForm, use it once, then do a search and replace in the code
> > > > for the number (e.g. search for "Uf1" in the names called
> > > > "Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
> > > > the code again for the next UserForm and do this for each new
> > > > UserForm.
>
> > > > This would speed up the work a hundred fold, plus it avoids or limits
> > > > the chances for spelling errors.
>
> > Bob, thank you for your response. I don't really know what Designer
> > is and I'm afraid that what you suggest is way over my head and I
> > think it would be more prudent to do it the old fashion way.
>
> > Thanks none the less.
>
> > Hector- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

It is both Susan. They are not preserved, and some properties cannot be
changed at run time (some, such as caption where applicable, you can). Name
is obviously one of these.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Susan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> well, i tried this code:
>
> Option Explicit
>
> Sub userform_initialize()
>
> Dim oControl As Control
> Dim StartNumber As Long
>
> For Each oControl In Me.Controls
> If TypeOf oControl Is msforms.CheckBox Then
> StartNumber = 1
> oControl.Name = "Userform2_Checkbox" & StartNumber
> StartNumber = StartNumber + 1
> End If
> Next oControl
>
> End Sub
>
> & i get an error saying: " Run-time error '382': Could not set the
> Name property. Can not set property at runtime."
> so i guess that's why he's saying you can't do it.
> susan
>
>
> On Mar 5, 3:09 pm, "Susan" <bogenex...@aol.com> wrote:
>> i think (not sure) that bob's saying that while my approach will/might
>> work, the changes won't be saved............... (i'm going to test
>> this myself).
>> by "designer" mode i believe he just means to have the VBA editor open
>> & manually change all the names in the properties box.
>> susan
>>
>> On Mar 5, 2:52 pm, "hdf" <perfi...@gmail.com> wrote:
>>
>>
>>
>> > On Mar 5, 8:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>>
>> > > The problem is that just changing the names isn't preserved, you need
>> > > to do
>> > > it via Designer.
>>
>> > > Something like this
>>
>> > > Dim oVBProj As Object
>> > > Dim oVBComp As Object
>> > > Dim ctl As Control
>> > > Dim i As Long
>>
>> > > 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 TypeName(ctl) = "CheckBox" Then
>> > > i = i + 1
>> > > ctl.Name = "ChkBx_" & oVBComp.codemodule.Name &
>> > > "_" & i
>> > > ctl.ControlSource = "=A1" '"=ChkBx_" &
>> > > oVBComp.codemodule.Name & "_Link" & i
>> > > End If
>> > > Next
>> > > End If
>> > > Next
>>
>> > > --
>> > > ---
>> > > HTH
>>
>> > > Bob
>>
>> > > (there's no email, no snail mail, but somewhere should be gmail in my
>> > > addy)
>>
>> > > "hdf" <perfi...@gmail.com> wrote in message
>>
>> > >news:(E-Mail Removed)...
>>
>> > > > On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>> > > >> Why would you not just set these as required in design mode?
>>
>> > > >> --
>> > > >> ---
>> > > >> HTH
>>
>> > > >> Bob
>>
>> > > > I'm trying to avoid manually having to write the name and range
>> > > > name
>> > > > in controlsource over and over 80 userforms x 20 chkboxes per form
>> > > > x 2
>> > > > changes per userform.
>>
>> > > > By creating code I can simply create one set of instructions for
>> > > > the
>> > > > first UserForm, use it once, then do a search and replace in the
>> > > > code
>> > > > for the number (e.g. search for "Uf1" in the names called
>> > > > "Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
>> > > > the code again for the next UserForm and do this for each new
>> > > > UserForm.
>>
>> > > > This would speed up the work a hundred fold, plus it avoids or
>> > > > limits
>> > > > the chances for spelling errors.
>>
>> > Bob, thank you for your response. I don't really know what Designer
>> > is and I'm afraid that what you suggest is way over my head and I
>> > think it would be more prudent to do it the old fashion way.
>>
>> > Thanks none the less.
>>
>> > Hector- Hide quoted text -
>>
>> > - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>

On Mar 5, 9:19 pm, "Susan" <bogenex...@aol.com> wrote:
> well, i tried this code:
>
> Option Explicit
>
> Sub userform_initialize()
>
> Dim oControl As Control
> Dim StartNumber As Long
>
> For Each oControl In Me.Controls
> If TypeOf oControl Is msforms.CheckBox Then
> StartNumber = 1
> oControl.Name = "Userform2_Checkbox" & StartNumber
> StartNumber = StartNumber + 1
> End If
> Next oControl
>
> End Sub
>
> & i get an error saying: " Run-time error '382': Could not set the
> Name property. Can not set property at runtime."
> so i guess that's why he's saying you can't do it.
> susan
>
> On Mar 5, 3:09 pm, "Susan" <bogenex...@aol.com> wrote:
>
> > i think (not sure) that bob's saying that while my approach will/might
> > work, the changes won't be saved............... (i'm going to test
> > this myself).
> > by "designer" mode i believe he just means to have the VBA editor open
> > & manually change all the names in the properties box.
> > susan
>
> > On Mar 5, 2:52 pm, "hdf" <perfi...@gmail.com> wrote:
>
> > > On Mar 5, 8:34 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
>
> > > > The problem is that just changing the names isn't preserved, you need to do
> > > > it via Designer.
>
> > > > Something like this
>
> > > > Dim oVBProj As Object
> > > > Dim oVBComp As Object
> > > > Dim ctl As Control
> > > > Dim i As Long
>
> > > > 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 TypeName(ctl) = "CheckBox" Then
> > > > i = i + 1
> > > > ctl.Name = "ChkBx_" & oVBComp.codemodule.Name & "_" & i
> > > > ctl.ControlSource = "=A1" '"=ChkBx_" &
> > > > oVBComp.codemodule.Name & "_Link" & i
> > > > End If
> > > > Next
> > > > End If
> > > > Next
>
> > > > --
> > > > ---
> > > > HTH
>
> > > > Bob
>
> > > > (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> > > > "hdf" <perfi...@gmail.com> wrote in message
>
> > > >news:(E-Mail Removed)...
>
> > > > > On Mar 5, 7:31 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> > > > >> Why would you not just set these as required in design mode?
>
> > > > >> --
> > > > >> ---
> > > > >> HTH
>
> > > > >> Bob
>
> > > > > I'm trying to avoid manually having to write the name and range name
> > > > > in controlsource over and over 80 userforms x 20 chkboxes per form x 2
> > > > > changes per userform.
>
> > > > > By creating code I can simply create one set of instructions for the
> > > > > first UserForm, use it once, then do a search and replace in the code
> > > > > for the number (e.g. search for "Uf1" in the names called
> > > > > "Uf1_name1...Uf1_name20" and Replace with "Uf2", etc.) and then run
> > > > > the code again for the next UserForm and do this for each new
> > > > > UserForm.
>
> > > > > This would speed up the work a hundred fold, plus it avoids or limits
> > > > > the chances for spelling errors.
>
> > > Bob, thank you for your response. I don't really know what Designer
> > > is and I'm afraid that what you suggest is way over my head and I
> > > think it would be more prudent to do it the old fashion way.
>
> > > Thanks none the less.
>
> > > Hector- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -

Susan,

Thanks for trying. I guess I'll just have to go the ole fashion way
and do it manually.

Hector