Linking textboxes to cells with formulae
 

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

Linking textboxes to cells with formulae
 

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


"David Coleman" wrote in message
...
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).

Linking textboxes to cells with formulae
 

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


"Rob van Gelder" wrote in message
...
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


"David Coleman" wrote in message
...
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).

Linking textboxes to cells with formulae
 

I know of no other way.

"David Coleman" wrote in message
...
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


"Rob van Gelder" wrote in message
...
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


"David Coleman" wrote in message
...
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).

Linking textboxes to cells with formulae
 

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

In article ,
says...
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).

Linking textboxes to cells with formulae
 

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


"Tushar Mehta" wrote in message
om...
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

In article ,
says...
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).

Linking textboxes to cells with formulae
 

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

In article ,
says...
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


"Tushar Mehta" wrote in message
om...
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

In article ,
says...
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).

Linking textboxes to cells with formulae
 

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)



"Tushar Mehta" wrote in message
om...
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

In article ,
says...
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


"Tushar Mehta" wrote in
message
om...
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

In article ,
says...
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).