Results 1 to 13 of 13
  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 in R1C1 (2000)

    Hi all,

    How do I change the following code to r1c1 where I can substitue variables for the row and column numbers?

    TextBox1.ControlSource = "=Data!a4"


    Thanks,
    Mike

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Textbox control source in R1C1 (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Mike

    OK so you want to allow the use of a variable control source for a text box, OK you really don't need R1C1 notation all you need to do is say something like:

    TextBox1.ControlSource = "=Data!" & Cells(lRowDesignator, iColumnDesignator)

    But if you want to use R1C1 noatation then your example would be:

    R4C1

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox control source in R1C1 (2000)

    R4C1 is the absolute reference. Relative references use square brackets around the numbers - R[4]C[1]. HTH
    Gre

  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: Textbox control source in R1C1 (2000)

    Thanks!

  5. #5
    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 in R1C1 (2000)

    Well, I still cannot get this to work...

    <pre>Private Sub ListBox1_Click()
    Dim bernadette As Integer
    Entry.Label15.Caption = ListBox1.Value
    For Each cell In ThisWorkbook.Sheets("Data").Range("b3:af3")
    If Str(cell.Value) = ListBox1.Value Then
    bernadette = cell.Column
    Exit For
    End If
    Next
    <font color=red>TextBox1.ControlSource = "=Data!R6C" & bernadette</font color=red>
    End Sub
    </pre>


    Any ideas?

    I get the attached message. (By the way how do you put the image into the post?)

    [i]Edited by me to add this[i]
    Nevermind about the image, I see Woody put it there just by attaching it!
    Attached Images Attached Images

  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 in R1C1 (2000)

    OK, a thorough search returned this reply from Andrew to a previous post which appends .Address to the end of the Cells(a,[img]/forums/images/smilies/cool.gif[/img] method which works well.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Textbox control source in R1C1 (2000)

    So you used
    <pre>TextBox1.ControlSource = Sheets("Data").Cells(6,bernadette).Address</pre>

    Is that correct? Now works for me, but I would have never thought to do it that way. That is totally bizzare! Glad you solved the problem: trying to figure out what was happening on my test sheet was really bugging me!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    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 in R1C1 (2000)

    That is exactly what I used. I was trying to make a userform similar to the ones in Access where you can click buttons to go to the next record or to the previous record. I thought it was sort of bizarre also!

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox control source in R1C1 (2000)

    Nothing bizarre about it, it makes sense. If you set the ControlSource property manually, you type Sheet1!$A$1 or some-such into the field in the properties sheet.

    In online help, it says: "If ControlSource contains a value other than an empty string, it identifies a linked cell or field."

    <pre>Sheets("Data").Cells(6,bernadette)
    </pre>


    is a range, not a string. To get the string that identifies the linked cell, you use the .Address property of that range.

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Textbox control source in R1C1 (2000)

    Yep, you're right: it works just like it should. The answer to the original post is to remove the equal sign. In other words,
    <pre>TextBox1.ControlSource = "=Data!a4"</pre>

    is incorrect; it will try to use the string that is in cell A4 as the control source address. It should be
    <pre>TextBox1.ControlSource = "Data!a4"</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox control source in R1C1 (2000)

    This illustrates a good technique for getting detailed information about how a property should be used. For example, try setting the property manually if possible (by typing into the property sheet) until you get it to work, then inspect the result:

    <pre>Debug.Print frmForm1.txtBox1.ControlSource
    </pre>


    This really beats trying to code it the way you thought it should work and beating your head against the wall.

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Textbox control source in R1C1 (2000)

    When you get old like me <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, sometime you just can't see <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> I set it manually in the properties window, viewed it in the watch window, then changed it incorrectly with an extra equal sign, "=Sheet!Range". To make matters worse, the range had a string in it which just happened to be a valid address, "L10", so I was like Clancy on the RTA (you have to be old to get that) <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Textbox control source in R1C1 (2000)

    Sammy,

    Was Clancy the one who didn't have enough money to get off the train? I always wondered why, if his wife could slip him a bag with lunch as the train went by, she didn't stick 15 cents in the bag also. Or was that someone else on the MTA?

    Fred

Posting Permissions

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