Control Source Property Excel Forms 1

[robertborchardt]

In an excel spreadsheet there are approx 20 worksheets that are somewhat tied together. When the user is on a sheet, I would like the form to pop up and pull data from a specific sheet and range. For some reason I cannot get the data to pull over to the form. I am using the code below:
TextBox1.ControlSource = "Sheet4!E$205"
TextBox1.Text = Sheet4.Range("E$205").Value
get runtime error '424' object required. I have tried using the below as well without any luck.

TextBox1.LinkedCell = "'Tally'!E205"
TextBox2.LinkedCell = "'Tally'!F205"
TextBox3.LinkedCell = "'Tally'!G205"
frm.TextBox1.Value = Worksheets("Tally").Cells(250, 5)
Any suggestions?

 

[SkipVought]

Hi,

There is worksheet notation, which you are using and VBA reference, for instance...

TextBox1.Text = Sheets("Sheet4").Range("E205").Value

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robertborchardt]

Thank you Skip, I actually tried to use your code, without success earlier yesterday. I was fried with all of the combinations I had tried. What I ended up having to use was (where scorecard is the name of the form):

ScoreCard.TextBox1.Value = Worksheets("Tally").Range("e205")
ScoreCard.TextBox2.Value = Worksheets("Tally").Range("f205")
ScoreCard.TextBox3.Value = Worksheets("Tally").Range("g205")

robert