Automatic margin/profit calculator VBA

[flea80]

So I have created this small user form to calculate the sales price and profit by filling in the cost and margin.
That works fine.
What I want next is that if I change the sales price the values of margin and profit automatically change.
Or if I change profit then margin and sales price should change.
Textbox1 is cost, textbox2 is margin, etc.

How can I solve this, because now I'm facing issue with the "change" event.
And if you have tips to write better code please let me know!

Private Sub TextBox2_Change()
On Error Resume Next
If TextBox2.Value = "" Then
TextBox3.Value = ""
TextBox4.Value = ""

Else
TextBox3.Value = Round((TextBox1.Value / ((100 - TextBox2.Value) / 100)), 0)
TextBox4.Value = TextBox3.Value - TextBox1.Value

End If
End Sub

Private Sub TextBox1_Change()
On Error Resume Next
TextBox3.Value = Round((TextBox1.Value / ((100 - TextBox2.Value) / 100)), 0)
TextBox4.Value = TextBox3.Value - TextBox1.Value

End Sub

 

[Micron]

That issue with the Change event is what - change event is called for controls whose value you're changing in code? Consider using the AfterUpdate event instead, but you will have to exit the control in order to cause the event.

 

[flea80]

There is no other way like "if value of sales price has changed, then recalculate margin and profit"?
Putting this code in the userform and not a textbox change event?

 

[Micron]

Unlike an Access form, an Excel userform has no useful events for this - unless perhaps you want to use key or mouse events, but that would make no sense.

Putting this code in the userform and not a textbox change event?

Not sure you understand the scope of code. Userform control event code is in the userform module. What's wrong with using the AfterUpdate event of a textbox? Your code for your logical tests

if value of sales price has changed, then recalculate margin and profit

can go anywhere that provides a suitable event. You've seen the results of the change event becoming recursive when you change other controls that also use that event (at least I gather you have, but you didn't acknowledge that) and AFAIK there's no way to stop that. So I suggested the AfterUpdate event and so far, am sticking with that.

EDIT - even better is the fact that if you change a textbox's value via code and that control does not have the focus, the AfterUpdate won't fire. At least that's my recollection.

 

[flea80]

I have changed them to "AutoUpdate" and it all works now. Just filling in the numbers you want in a textbox and press TAB give the result I needed.
Thank you!

 

[Micron]

You set what to 'autoupdate'? I looked but don't see a property like that for a userform or a textbox.
Thanks.

 

[flea80]

AfterUpdate. **** autocorrect lol.

 

[Micron]

Why you credited me with the solution makes sense now! Thanks.

 

[flea80]

Lol that explains.
Quite new to programming vba so this was helpful.
Cheers.

 

[Micron]

As a beginner, you might find this useful

Office Visual Basic for Applications (VBA) reference

This reference is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

learn.microsoft.com

The site can also be frustrating, because the search feature is too broad and sometimes when you don't know what you don't know, you don't know where to look!