Linking textboxes to cells with formulae

  • Thread starter Thread starter David Coleman
  • Start date Start date
D

David Coleman

Hi

I've a cell on a sheet (call it a10) that contains a forumla to sum various
other functions on the sheet

eg =a1*10+b1*20+c1*30

The values of a1, b1 & c1 are set on an userform by using
textbox??.controlsource = "a1" etc

Textbox4 needs to show the result of the formula in a10. Easy, I thought -
set textbox4.controlsource = "a10"

Whilst this works initially, as soon as the formula is re-calculated ('cos
the user changes a textbox) the value of textbox4 is stored in a10 (ie the
resultant value overwrites the formula). Therefore, no other changes are
ever reflected.

Is there a way to stop this? The user may change any one (or more or all)
of the values in the data entry textboxes and I'd like the calculated total
to always show in textbox4.

Thanks in advance

David


BTW, all the data entry text boxes are contained within a frame, the total
box is located outside of that frame on the userform. Textbox4 is not
enabled (I don't want the user changing the total manually).
 
Add the following code to the userform:

Private Sub TextBox1_AfterUpdate()
TextBox4.Text = Range("A10").Value
End Sub

Private Sub TextBox2_AfterUpdate()
TextBox4.Text = Range("A10").Value
End Sub

Private Sub TextBox3_AfterUpdate()
TextBox4.Text = Range("A10").Value
End Sub

Then set the Locked property on TextBox4 to True
 
Is there a nicer way than this? This one I knew of but, with 32 text boxes
that can affect the result one, it didn't seem the tidiest method.....

Thanks

David
 
I know of no other way.

David Coleman said:
Is there a nicer way than this? This one I knew of but, with 32 text boxes
that can affect the result one, it didn't seem the tidiest method.....

Thanks

David
Click to expand...
 
I'm curious as to why you are using an userform for data entry.
Wouldn't it be easier to just enter data into the appropriate worksheet
cells?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Hi Tushar

Fundamentally, because I can't trust the users - they're complete
technophobes and they'd only manage to enter the data in the wrong cells or
insert invalid values or some such thing. Been there, done that - learnt to
add application.visible = false a long time ago.....

Regards

David
 
Fascinating. Don't mean to get into an argument on a subject on which
we apparently disagree at a rather fundamental level, but how do your
incompetent and untrustworthy users know which textbox to use if they
can't figure out which cell to use?

I would imagine that between protecting / locking cells and specifying
appropriate data validation criteria there is little that your use of
an userform can possibly add.

But, it's your system and your company's / client's nickel.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
It depends on the complexity of the sheet in question and its relationship
with many others - in this particular case, there are approximately 40
sheets with (for the sheet in question) some 48 columns by (potentially)
1600 rows and it's highly dependent on other sheets and other sheets are
highly dependent on this one! Furthermore, the headings for the columns in
question are potentially quite large so they'd end up having to scroll all
over the place - I'm not completely averse to letting users near the real
data but I've seen what some of them can do...

BTW, I'm only writing this for a friend as a favour (my real job is a
sysadmin) - my coding is never going to be the best but it normally ends up
reliable and (vaguely) efficient....

I think we'll just agree to dis-agree at this point ;o)
 
Back
Top