Can I use VBA to change name and controlsource
 

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.

Can I use VBA to change name and controlsource
 

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" 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.

Can I use VBA to change name and controlsource
 

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" wrote in message
oups.com...
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.

Can I use VBA to change name and controlsource
 

On Mar 5, 7:31 pm, "Bob Phillips" 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.

Can I use VBA to change name and controlsource
 

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" wrote in message
oups.com...
On Mar 5, 7:31 pm, "Bob Phillips" 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.

Can I use VBA to change name and controlsource
 

On Mar 5, 8:34 pm, "Bob Phillips" 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" wrote in message

oups.com...

On Mar 5, 7:31 pm, "Bob Phillips" 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

Can I use VBA to change name and controlsource
 

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" wrote:
On Mar 5, 8:34 pm, "Bob Phillips" 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" wrote in message

roups.com...

On Mar 5, 7:31 pm, "Bob Phillips" 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 -

Can I use VBA to change name and controlsource
 

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" 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" wrote:



On Mar 5, 8:34 pm, "Bob Phillips" 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" wrote in message

roups.com...

On Mar 5, 7:31 pm, "Bob Phillips" 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 -

Can I use VBA to change name and controlsource
 

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" wrote in message
ps.com...
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" 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" wrote:



On Mar 5, 8:34 pm, "Bob Phillips" 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" wrote in message

roups.com...

On Mar 5, 7:31 pm, "Bob Phillips" 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 -

Can I use VBA to change name and controlsource
 

On Mar 5, 9:19 pm, "Susan" 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" 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" wrote:

On Mar 5, 8:34 pm, "Bob Phillips" 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" wrote in message

roups.com...

On Mar 5, 7:31 pm, "Bob Phillips" 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

Can I use VBA to change name and controlsource
 

You mean each one by hand? Good luck!

--
---
HTH

Bob

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



"hdf" wrote in message
oups.com...
On Mar 5, 8:34 pm, "Bob Phillips" 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" wrote in message

oups.com...

On Mar 5, 7:31 pm, "Bob Phillips" 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