Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ControlSource and Localization Issue?? (run-time error '380' Could not set the ControlSource property) | Excel Programming | |||
.ControlSource | Excel Programming | |||
Change UserForm ControlSource with VBA | Excel Discussion (Misc queries) | |||
Data Validation & ControlSource & Change event | Excel Programming | |||
Controlsource | Excel Programming |