Hi Guys

I've hit a bit of a problem and am hoping that you can help...

I've got a series of textboxes on a userform that I need to dynamically link
to various cells on the activesheet. Normally, this isn't an issue as

stagedetails.TextBox2.ControlSource = "D" & workingrow works
happily

However, what I need to do is specifiy both the column and row by variables,
using integers for both options. Again, I could normally use the Cells(x,y)
syntax but

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells(workingrow, x + 2)

gives a "Runtime error '380': Could not set the ControlSource Property.
Invalid property value."

Now I know I could hack a routine together to convert integers to column
letters but it all seems a tad excessive - any ideas?

Many thanks

David

Try:

stagedetails.TextBox2.Text = ActiveSheet.Cells(workingrow,
x + 2).Value

Let me know if that works, 'cuz I'm just taking a guess
here...

-----Original Message-----
Hi Guys

I've hit a bit of a problem and am hoping that you can
help...

I've got a series of textboxes on a userform that I need
to dynamically link
to various cells on the activesheet. Normally, this
isn't an issue as

stagedetails.TextBox2.ControlSource = "D" &
workingrow works
happily

However, what I need to do is specifiy both the column
and row by variables,
using integers for both options. Again, I could normally
use the Cells(x,y)
syntax but

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells
(workingrow, x + 2)

gives a "Runtime error '380': Could not set the
ControlSource Property.
Invalid property value."

Now I know I could hack a routine together to convert
integers to column
letters but it all seems a tad excessive - any ideas?

Many thanks

David


.

Thanks for that but it doesn't achieve the required linking between the text
box and the spreadsheet cell...

I could use this methodology but it's very messy as I'd have to manually
copy all the data between the cell & textbox whenever something changes and,
with 40+ textboxes, it's not my ideal way of spending a day coding!

Thanks anyway

David




"JoJo the Dancing Monkey" wrote in
message ...
Try:

stagedetails.TextBox2.Text = ActiveSheet.Cells(workingrow,
x + 2).Value

Let me know if that works, 'cuz I'm just taking a guess
here...

-----Original Message-----
Hi Guys

I've hit a bit of a problem and am hoping that you can
help...

I've got a series of textboxes on a userform that I need
to dynamically link
to various cells on the activesheet. Normally, this
isn't an issue as

stagedetails.TextBox2.ControlSource = "D" &
workingrow works
happily

However, what I need to do is specifiy both the column
and row by variables,
using integers for both options. Again, I could normally
use the Cells(x,y)
syntax but

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells
(workingrow, x + 2)

gives a "Runtime error '380': Could not set the
ControlSource Property.
Invalid property value."

Now I know I could hack a routine together to convert
integers to column
letters but it all seems a tad excessive - any ideas?

Many thanks

David


.

David,

Problem is that controlsource is an address string, whereas
Cells(workingrow, x + 2) gives the defualt Value property. IT can be
circumvented with

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells(workingrow, x +
2).address

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Coleman" wrote in message
...
Hi Guys

I've hit a bit of a problem and am hoping that you can help...

I've got a series of textboxes on a userform that I need to dynamically
link
to various cells on the activesheet. Normally, this isn't an issue as

stagedetails.TextBox2.ControlSource = "D" & workingrow works
happily

However, what I need to do is specifiy both the column and row by
variables,
using integers for both options. Again, I could normally use the
Cells(x,y)
syntax but

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells(workingrow, x + 2)

gives a "Runtime error '380': Could not set the ControlSource Property.
Invalid property value."

Now I know I could hack a routine together to convert integers to column
letters but it all seems a tad excessive - any ideas?

Many thanks

David

Thanks for that Bob - I knew it was something simple but hadn't thought of
the .address option.

Regards

David




"Bob Phillips" wrote in message
...
David,

Problem is that controlsource is an address string, whereas
Cells(workingrow, x + 2) gives the defualt Value property. IT can be
circumvented with

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells(workingrow, x +
2).address

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"David Coleman" wrote in message
...
Hi Guys

I've hit a bit of a problem and am hoping that you can help...

I've got a series of textboxes on a userform that I need to dynamically
link
to various cells on the activesheet. Normally, this isn't an issue as

stagedetails.TextBox2.ControlSource = "D" & workingrow
works
happily

However, what I need to do is specifiy both the column and row by
variables,
using integers for both options. Again, I could normally use the
Cells(x,y)
syntax but

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells(workingrow, x +
2)

gives a "Runtime error '380': Could not set the ControlSource Property.
Invalid property value."

Now I know I could hack a routine together to convert integers to column
letters but it all seems a tad excessive - any ideas?

Many thanks

David

Change the code to

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells
(workingrow, x + 2).Address(, , xlA1, True)


Kevin Beckham

-----Original Message-----
Hi Guys

I've hit a bit of a problem and am hoping that you can
help...

I've got a series of textboxes on a userform that I need
to dynamically link
to various cells on the activesheet. Normally, this
isn't an issue as

stagedetails.TextBox2.ControlSource = "D" &
workingrow works
happily

However, what I need to do is specifiy both the column
and row by variables,
using integers for both options. Again, I could normally
use the Cells(x,y)
syntax but

stagedetails.TextBox2.ControlSource = ActiveSheet.Cells
(workingrow, x + 2)

gives a "Runtime error '380': Could not set the
ControlSource Property.
Invalid property value."

Now I know I could hack a routine together to convert
integers to column
letters but it all seems a tad excessive - any ideas?

Many thanks

David


.