Textbox Control Source Syntax

Topic

New Reply

Hi all,

When using the textbox.control source in the properties panel of a user form, I seem to be able to tie the control source to a cell in the active workbook, i.e. “=C2”. Is there a way to define which sheet in the workbook the control source is linked to? I have tried syntax similar to links, such as “=Sheet1!C2”, but to no avail. Excel 2000/W98.

Many thanks,

Reply | Quote

Replies

Mike,

You can make use of the controlsource property in ‘run-time’ putting the code in the form activate event (or initiate):

Private Sub UserForm_Activate()
TextBox1.ControlSource = Sheets(“Sheet1”).Range(“C1”)
End Sub

Reply | Quote

Hi Hans,

I tried this and it gives me “Error code 380: Invalid property value”. I tried on a new workbook in hopes that it may be something with my current form, but I get the same thing. Any ideas?

Thanks,

Reply | Quote

Blazes, must be time for some time off…this is the code I put in the properties panel for the control source which worked great…

=sheet3!c1

Reply | Quote

This is strange Mike. In fact, when I enter Sheet1!C1 in the controlsource property of the property window of the Textbox on the Userform, then everything is OK. (No quotes!). I just proposed this method because it’s equivalent to the ‘design time’ method. I am using Excel 2000 and Win98, just like you. I first tried out the method I proposed in my previous post, it worked fine.
To let it work, I inserted a Userform and a module. In the module I have a very small macro that only contains the code Userform1.Show to display the userform. I do not get an error.

To see if the property exists, use the intellisense (by default this should be active). When you have added a Textbox to your userform, say Textbox1, then when you type in some code in the Userform_Activate event, like TextBox1. , then after pressing the “.”, you get a list of available methods and properties. If ‘ControlSource’ is in that list, I don’t know why you get an error.

Reply | Quote

Thanks for the thoughts on this Hans. As you mentioned in an earlier post on forecasting, I think I have probably “tortured” my form and the various controls therein. Regardless of what caused it, I finally got the “design” time method of the control source to work. Many thanks for your input(s).

By the way, I do get the property listed after I press the “.” in the listbox containing the properties for the TextBox. Go figure…tortured control sources?

Reply | Quote

Hi all,

Just another bit of information…

When I used the ControlSource property in the properties window for a new workbook, the code =Sheets1!C1 worked fine. However, when I tried to go back to my original workbook and use similar code, I received the same error code again.

So I deleted the original sheet that the ControlSource property referred to and copied the cell information to a new sheet. Thus far, it seems to have eliminated the problem. I can only figure that some of the data in the original sheet was corrupted somehow, because the ControlSource property seems to work fine with the new sheet.

FYI,

Reply | Quote