How to display =x/y result in a user form

I have tried to display the result of a percentage calculation in a text box
built into a custom user form. In the properties section for the text box I
use the ControlSource property to link the text box to the cell in my
spreadsheet that contains the calculation result I want displayed.

I use the same property to display both x and y in 2 other text boxes on the
user form and have no problems with that at all.

Unfortunately I find that the calculation display goes wrong. I don't get
any error messages and my macro doesn't trip over but for some reason the
calculation formula disappears on the sheet the userform is linked to. After
a few times of running my macro (not always the same number), I notice the
calculation result shown in the user form has stopped changing and when I
check in the source sheet I find that the calculation formula is no longer in
the cell, it has been replaced by a number (not a random number but the
number calculated the last time the formula worked properly).

I have stepped through my code one by one and the cell the calculation is in
is not referred to anywhere else in the code, and nowhere else in any cells
in the workbook either. The only item that uses that cell reference is the
ControlSource I use for the user form.

Can anyone suggest why this is happening?

Maybe the ControlSource property is not suitable for displaying calculation
results in userforms, if so, can anyone suggest a more appropriate way to do
it??

ControlSource updates in both directions. You can see this if you type some
text in your textbox. So in effect the textbox writes text back to the cell
when updated, replacing the formula with a value.

Although you could get around this with convoluted means, I would remove
ControlSource and update when your calc is done.

' do calc stuff
Me.TextBox1.Text = ActiveSheet.Range("A1").Value

However I think I would use a Label, perhaps white with a border to look
like a textbox

Me.Label1.Caption = ActiveSheet.Range("A1")

Regards,
Peter T


"AndyRoo" <AndyRoo@discussions.microsoft.com> wrote in message
news:EFD375DB-C0C5-4293-8FA5-B6C9B92F4168@microsoft.com...
> I have tried to display the result of a percentage calculation in a text
box
> built into a custom user form. In the properties section for the text box
I
> use the ControlSource property to link the text box to the cell in my
> spreadsheet that contains the calculation result I want displayed.
>
> I use the same property to display both x and y in 2 other text boxes on
the
> user form and have no problems with that at all.
>
> Unfortunately I find that the calculation display goes wrong. I don't get
> any error messages and my macro doesn't trip over but for some reason the
> calculation formula disappears on the sheet the userform is linked to.
After
> a few times of running my macro (not always the same number), I notice the
> calculation result shown in the user form has stopped changing and when I
> check in the source sheet I find that the calculation formula is no longer
in
> the cell, it has been replaced by a number (not a random number but the
> number calculated the last time the formula worked properly).
>
> I have stepped through my code one by one and the cell the calculation is
in
> is not referred to anywhere else in the code, and nowhere else in any
cells
> in the workbook either. The only item that uses that cell reference is
the
> ControlSource I use for the user form.
>
> Can anyone suggest why this is happening?
>
> Maybe the ControlSource property is not suitable for displaying
calculation
> results in userforms, if so, can anyone suggest a more appropriate way to
do
> it??

Peter,

Thanks very much, I have used the label approach and it solves the problem.
Now I know why John Walkenbach said be careful with ControlSource in the 'VBA
for Dummies' book I read!!

Regards

Andrew

"Peter T" wrote:

> ControlSource updates in both directions. You can see this if you type some
> text in your textbox. So in effect the textbox writes text back to the cell
> when updated, replacing the formula with a value.
>
> Although you could get around this with convoluted means, I would remove
> ControlSource and update when your calc is done.
>
> ' do calc stuff
> Me.TextBox1.Text = ActiveSheet.Range("A1").Value
>
> However I think I would use a Label, perhaps white with a border to look
> like a textbox
>
> Me.Label1.Caption = ActiveSheet.Range("A1")
>
> Regards,
> Peter T
>
>
> "AndyRoo" <AndyRoo@discussions.microsoft.com> wrote in message
> news:EFD375DB-C0C5-4293-8FA5-B6C9B92F4168@microsoft.com...
> > I have tried to display the result of a percentage calculation in a text
> box
> > built into a custom user form. In the properties section for the text box
> I
> > use the ControlSource property to link the text box to the cell in my
> > spreadsheet that contains the calculation result I want displayed.
> >
> > I use the same property to display both x and y in 2 other text boxes on
> the
> > user form and have no problems with that at all.
> >
> > Unfortunately I find that the calculation display goes wrong. I don't get
> > any error messages and my macro doesn't trip over but for some reason the
> > calculation formula disappears on the sheet the userform is linked to.
> After
> > a few times of running my macro (not always the same number), I notice the
> > calculation result shown in the user form has stopped changing and when I
> > check in the source sheet I find that the calculation formula is no longer
> in
> > the cell, it has been replaced by a number (not a random number but the
> > number calculated the last time the formula worked properly).
> >
> > I have stepped through my code one by one and the cell the calculation is
> in
> > is not referred to anywhere else in the code, and nowhere else in any
> cells
> > in the workbook either. The only item that uses that cell reference is
> the
> > ControlSource I use for the user form.
> >
> > Can anyone suggest why this is happening?
> >
> > Maybe the ControlSource property is not suitable for displaying
> calculation
> > results in userforms, if so, can anyone suggest a more appropriate way to
> do
> > it??
>
>
>