Can I use VBA to change name and controlsource

[hdf]

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.

 

[Susan]

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

 

[Bob Phillips]

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]

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

--

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 Phillips]

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]

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)

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

 

[Susan]

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

 

[Susan]

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

 

[Bob Phillips]

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)

 

[hdf]

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

Susan,

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

Hector

 

[Bob Phillips]

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)