Error 380 trying to set Control Source

[sharky12345]

I'm trying to use VBA to set the Control Source to a Textbox by using this;

TextBox1.ControlSource = "TabletResults!B10"

but I get an error 380.....I have also tried this with the same result;

TextBox1.ControlSource = "Sheet3!B10"

Can anyone point me to why it fails?

 

[RoryA]

Are you certain those are valid sheet names in the workbook with the control?

 

[sharky12345]

Yes - double checked that.

 

[RoryA]

Where is the textbox?

 

[sharky12345]

On a userform

 

[RoryA]

Do you get the same error with this:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("TabletResults")
TextBox1.ControlSource = "'" & ws.Name & "'!B10"

 

[sharky12345]

With that I got a run time error 9, subscript out of range.....

but changing it to this worked;

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Tablet Results")
TextBox1.ControlSource = "'" & ws.Name & "'!B10"

 

[RoryA]

So when I said "Are you certain those are valid sheet names"...

 

[sharky12345]

This;

TextBox1.ControlSource = "TabletResults!B10"

didn't work when I tried this;

TextBox1.ControlSource = "Tablet Results!B10"

that's why I said the sheet names were correct because I tried it with spaces and it still didn't work.

 

[Norie]

If there are spaces in a sheet name you need to enclose it in '.