Textbox control source in R1C1 (2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

Thanks!

Reply | Quote

Well, I still cannot get this to work…

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
TextBox1.ControlSource = "=Data!R6C" & bernadette
End Sub

Any ideas?

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

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

Reply | Quote

OK, a thorough search returned this reply from Andrew to a previous post which appends .Address to the end of the Cells(a, method which works well.

Reply | Quote

So you used

TextBox1.ControlSource = Sheets("Data").Cells(6,bernadette).Address

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!

Reply | Quote

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!

Reply | Quote

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.”

Sheets("Data").Cells(6,bernadette)

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

Reply | Quote

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,

TextBox1.ControlSource = "=Data!a4"

is incorrect; it will try to use the string that is in cell A4 as the control source address. It should be

TextBox1.ControlSource = "Data!a4"

Reply | Quote

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:

Debug.Print frmForm1.txtBox1.ControlSource

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

Reply | Quote

When you get old like me , sometime you just can’t see 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) –Sam

Reply | Quote

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

Reply | Quote