Controlsource

D

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
 
J

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

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
 
B

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)
 
D

David Coleman

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

Regards

David
 
K

Kevin Beckham

Change the code to

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


Kevin Beckham
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top