last piece of the puzzle...controlsource in textbox
 

I have run into a problem. I have a userform which has several textboxes which currently display figures from a worksheet cell with a formula in it. Unfortunately, the formula is automatically replaced by the value in the textbox which defeats the purpose. What I want to do is have the textbox display a value based on a formula which references the active workbook.

I am a newbie so detailed code is appreciated. Additionally, I have 3 years worth of textboxes with 36 rows in each year. If there is anyway to do one set of code for each year as opposed to code for each textbox that would be helpfu

Thanks.

last piece of the puzzle...controlsource in textbox
 

Here is a method by John Walkenbach for handling multiple forms controls of
the same type with a single macro.

http://j-walk.com/ss/excel/tips/tip44.htm
Handle Multiple UserForm Buttons With One Subroutine

unfortunately, this doesn't work with events provided by the controls object
such as the exit and enter events.

It only works with native events like click and change.

You can't have a formula in a control source linked cell. As you have
noted, the update of the textbox wipes out the formula.

You need to update the textbox or the cell with an event.

Code is usually built to support some functionality. You haven't described
anything specific.

--
Regards,
Tom Ogilvy



"bruce forster" wrote in message
...
I have run into a problem. I have a userform which has several textboxes
which currently display figures from a worksheet cell with a formula in it.
Unfortunately, the formula is automatically replaced by the value in the
textbox which defeats the purpose. What I want to do is have the textbox
display a value based on a formula which references the active workbook.

I am a newbie so detailed code is appreciated. Additionally, I have 3
years worth of textboxes with 36 rows in each year. If there is anyway to
do one set of code for each year as opposed to code for each textbox that
would be helpful

Thanks.

last piece of the puzzle...controlsource in textbox
 

Withy 3 years of data, 36 textboxes per year, wouldn't it be more
appropriate to use standard worksheet functionality rather than a userform.

--

HTH

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

"bruce forster" wrote in message
...
I have run into a problem. I have a userform which has several textboxes
which currently display figures from a worksheet cell with a formula in it.
Unfortunately, the formula is automatically replaced by the value in the
textbox which defeats the purpose. What I want to do is have the textbox
display a value based on a formula which references the active workbook.

I am a newbie so detailed code is appreciated. Additionally, I have 3
years worth of textboxes with 36 rows in each year. If there is anyway to
do one set of code for each year as opposed to code for each textbox that
would be helpful

Thanks.