• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

Calculated field in userform

I have a userform with two textboxes. Textbox1 have controlsource C2, Textbox2 have controlsource C3. How can I show the sum of this two fields on the userform. I tried with another Textbox with controlsource cell C4 where I put =C2+C3. But the value from the userform overwrites the formula in C4 so this does'nt work. Appreciate an tips.
0
hallpett
Asked:
hallpett
  • 4
  • 3
1 Solution
 
krishnakrkcCommented:
Hi,

what about

TextBox3.Value = TextBox1.Value + TextBox2.Value

0
 
hallpettAuthor Commented:
I'm not so experienced with vba in excel. Where should I put this line to make it work?
0
 
GrahamSkanCommented:
Perhaps You should set the Locked property of TextBox3 to True, so that it can't be edited
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
hallpettAuthor Commented:
"Perhaps You should set the Locked property of TextBox3 to True, so that it can't be edited"
Can't find a Locked property but I have tried to set the Enabled property to false. Don't work. The value you see when you open the user form will still overwrite the formula in the controlsource cell.
0
 
GrahamSkanCommented:
How does that value get there? In my tests, the value is taken from the cell when the form is initialised.
0
 
hallpettAuthor Commented:
The value is taken from the cell when the form is opened, but when you change value in textbox1 (cell C2) or textbox2 (cell C3), then textbox3 (cell C4, with formula =C2+C3) get a new value from cell C4 that textbox3 overwrites back to C4. I'm propably not the best to explain but hope this is understandable.
0
 
GrahamSkanCommented:
Ah. I see now

The cell formula is replaced by the textbox value. I think you'll have to remove the control source for TextBox3 and use the Change events

Option Explicit

Private Sub TextBox1_Change()
    TextBox3.Value = Val(TextBox1.Value) + Val(TextBox2.Value)
End Sub

Private Sub TextBox2_Change()
    TextBox3.Value = Val(TextBox1.Value) + Val(TextBox2.Value)
End Sub

Open in new window

0
 
hallpettAuthor Commented:
Worked just fine. Thank you!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now