Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Textbox Control Source Syntax

    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,

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox Control Source Syntax

    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

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox Control Source Syntax

    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,

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hot Diggety Dog! I got it!

    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

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox Control Source Syntax

    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.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox Control Source Syntax

    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?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hot Diggety Dog, I thought I had it

    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •