Programatically set control source - not working
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 |
Programatically set control source - not working
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 |
Programatically set control source - not working
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..... |
All times are GMT +1. The time now is 09:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com