Hi,

I have two (related issues). Firstly that I have 12 user forms, each
with upwards of 600 text boxes (yes i'm creating a calendar!). Each of
these needs the control source setting. This is issue 1!

To solve this I figured the easiest way was to name these textboxes in
numerical order and then programatically set the control source.
(naming them still takes ages but i think it's the better choice!)

So my text boxes are named T45, T46, T47 etc and the controlsource
therefore needs to be set to Sheet4!g45, sheet4!g46, sheet4!g47 etc

So i quickly wrote the below code, which runs fine but does nothing to
change the controlsource of the textboxes! Grrrr!

Private Sub UserForm_Initialize()
Dim i, ctrl, srce, K
K = 70
For i = 45 To 99
srce = ("Sheet4!g" & K)
ctrl = ("T" & i)
Debug.Print ctrl
Debug.Print srce
Me.Controls(ctrl).ControlSource = (srce)
K = K + 1
Next i


Can anybody suggest why this is doing absolutely NOTHING?! Thanks in
advance

I couldn't duplicate the problem. The SourceControl worked properly for me.
In your description you implied that your SourceControl cells would run
numerically the same as your text box numbers, but in the code one starts at
45 and the other at 70, so are you looking at the right cells to confirm the
link? Or was that just a "for example" in the explanation?

"anon" wrote:

Hi,

I have two (related issues). Firstly that I have 12 user forms, each
with upwards of 600 text boxes (yes i'm creating a calendar!). Each of
these needs the control source setting. This is issue 1!

To solve this I figured the easiest way was to name these textboxes in
numerical order and then programatically set the control source.
(naming them still takes ages but i think it's the better choice!)

So my text boxes are named T45, T46, T47 etc and the controlsource
therefore needs to be set to Sheet4!g45, sheet4!g46, sheet4!g47 etc

So i quickly wrote the below code, which runs fine but does nothing to
change the controlsource of the textboxes! Grrrr!

Private Sub UserForm_Initialize()
Dim i, ctrl, srce, K
K = 70
For i = 45 To 99
srce = ("Sheet4!g" & K)
ctrl = ("T" & i)
Debug.Print ctrl
Debug.Print srce
Me.Controls(ctrl).ControlSource = (srce)
K = K + 1
Next i


Can anybody suggest why this is doing absolutely NOTHING?! Thanks in
advance

That was just an example, however i've now found my code does work -
the reason i thought it was not working is that it doesn't update the
text in the controlsource box in the properties window.

So before my code the control source of textbox1 is set to Sheet4!G1
I run my code to set it to Sheet4!H1
The textbox now links to Sheet4!H1(i have tested this by putting in a
value etc) however the controlsource in the properties window still
shows Sheet4!G1

I know this is very strange.....