Controlsource

[David Coleman]

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

 

[JoJo the Dancing Monkey]

Try:

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

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

 

[David Coleman]

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

 

[Bob Phillips]

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]

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

Regards

David

 

[Kevin Beckham]

Change the code to

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


Kevin Beckham