UserForm Calculations VBA

Question

Hi I want way to automatically calculate Price * Qty into my Amount textbox.  I have used these variables declared in my code

Note that I have six rows in the form hoping that different items may be selected for purchase which will give a line total in the amount end before it calculates the grand total I have done this code

Dim Price1 As Double

Dim Qty1 As Double

Dim Amount1 As Double

Private Sub cmdCheckOut_Click()

'       lblDate.Caption = Format(Date, "Medium Date")

Amount1 = txtAmt1.Text

Amount2 = txtAmt2.Text

Amount3 = txtAmt3.Text

Amount4 = txtAmt4.Text

Amount5 = txtAmt5.Text

Amount6 = txtAmt6.Text

lbltotal.Caption = (Amount1 + Amount2 + Amount3 + Amount4 + Amount5 + Amount6)

lbltotal.Caption = Format(lbltotal.Caption, "$#,##0.00")

please use this link to reach the design https://1drv.ms/x/s!AlF293aweqwLkU41VGBJamIWWhfR

Answer 1

Here is it: https://1drv.ms/f/s!AlF293aweqwLa8TUy4MoXLzv5Bs

Alright, here's the modified file.

https://www.dropbox.com/s/j7ca16v2hrm5s4o/0edc3f26-b59a-40bd-9b4e-a5e38910e385.xlsm?dl=1

IMHO a combo box is better for the parts... try it and have fun. ;-)

Andreas.

Answer 2

I have just started with VBA and have never in my tutorial created a class. I will need help with all that.

At first, try my code as I have suggest. Play around a little with your form, IMHO you'll love it. And you need a little motivation for the following. ;-)

Okay, let us start a simple as possible:

It is obviuous in such kind of forms that you have to calculate the amount from price and quantity in each row every time when a related control is changed.

So if you type something into txtPrice1 the Change event of the control is raised and any Change event routine that belongs to that control is called. Sounds a little cryptic, but it isn't. Here is a simple one:

Private Sub txtPrice1_Change()

  'Errors off, that's easier as to parse the content of the textboxes

  On Error Resume Next

  'Now clear the result, because the next line can fail

  Me.txtAmt1 = ""

  'Do the calculation

  Me.txtAmt1 = CDbl(Me.txtPrice1) * CDbl(Me.txtQty1)

End Sub

I think this should be clear and you should understand what's going on and how that works.

If not, remove all code from the Userform, paste in only that sub, place a breakpoint at the first line, run your form, type something into txtQty1, then in txtPrice1, debug the code and watch what happens.

Okay, next step: The calculation works if we change txtPrice1, now we must do the same if txtQty1 is changed, so add this one:

Private Sub txtQty1_Change()

  'Do the same as above

  txtPrice1_Change

End Sub

Alright, the 1st row is done. Now we need 5 more... that means in fact that you have to copy Sub txtPrice1_Change() and Sub txtQty1_Change() and replace any "1" with a "2" to get the second row to work.

And the 3rd. And the 4th. And the 5th. And the 6th.... And what do you have at the end? A lot of code and looks (more or less) all the same.

BTW, and what if you want to have a Userform that adds another row if the last one is filled?

Yes, that is possible!

But it's not so easy to implement and hard to understand for beginners. So place that on your ToDo list.

Okay, back to the code. How to prevent that we have so much code that looks all the same?

The trick is the keyword WithEvents!

Withevents works only in class modules.

(For completeness: A class module is obvious a class module, but a Userform is also a class module and the code module of a sheet is also a class module and finally the code module ThisWorkbook is also a class module! In all that modules you can use WithEvents)

Let us start with this one:

Public WithEvents Hugo As MSForms.TextBox

Create a class module, name it "HelloWorldClass", then write that line into a class module, then open the left dropdown on the top of the VBA editor und you can see "Class" and "Hugo":

Select "Hugo" and wham! the Change event routine is created autmatically. Add a MsgBox and your code is:

Public WithEvents Hugo As MSForms.TextBox

Private Sub Hugo_Change()

  MsgBox "Hello World"

End Sub

Okay, Hugo is a textbox, but not a real textbox now, we are at design time. We assign the real textbox later at run time and the code for that must be placed in the Userform:

'Declare the variable globale, because...

Dim HWC As HelloWorldClass

Private Sub UserForm_Initialize()

  '...if we declare the variable in here, the memory manager _

    destroys the variable after the sub has run!

  'Create a new class in memory

  Set HWC = New HelloWorldClass

  'Assign any textbox you like, e.g.

  Set HWC.Hugo = Me.txtPrice1

End Sub

Done. Run the form and write something into txtPrice1.... tadaa. It's magic. ;-)

I know you need some time to investigate my code, IMHO the best way it to place breakpoints at the first in any sub, run the form, if the code stops, debug it step by step, look what happens. Use also the Watchwindow, etc.

Never heared about debugging? Have a look here:

http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training.htm

Andreas.

Answer 3

Create a new class module, name it "MyClass" and paste in this code

--- schnipp ---

Option Explicit

Public Parent As nizamForm

Public WithEvents txtPrice As MSForms.TextBox

Public WithEvents txtQty As MSForms.TextBox

Public txtAmt As MSForms.TextBox

Private Sub txtPrice_Change()

  On Error Resume Next

  'Assume failure

  txtAmt = ""

  'Do the calculation

  txtAmt = CDbl(txtPrice) * CDbl(txtQty)

  'Update total

  Parent.UpdateTotal

End Sub

Private Sub txtQty_Change()

  'Same

  txtPrice_Change

End Sub

--- schnapp ---

Replace all code in your userform with the code below.

Andreas.

Option Explicit

Dim MyEvents As New Collection

Public Sub UpdateTotal()

  Dim Result As Double

  Dim i As Integer

  For i = 1 To 6

    If IsNumeric(Me.Controls("txtAmt" & i)) Then

      Result = Result + CDbl(Me.Controls("txtAmt" & i))

    End If

  Next

  Me.lbltotal = Format(Result, "0.00")

End Sub

Private Sub UserForm_Initialize()

  Dim i As Integer

  Dim This As MyClass

  For i = 1 To 6

    'Create a new handler

    Set This = New MyClass

    With This

      Set .Parent = Me

      Set .txtPrice = Me.Controls("txtPrice" & i)

      Set .txtQty = Me.Controls("txtQty" & i)

      Set .txtAmt = Me.Controls("txtAmt" & i)

    End With

    'Keep it alive

    MyEvents.Add This

  Next

End Sub

Hi Bernie hope you had time to look at my form using the link I provided. Am saying this because, you said I don't have txtAmt, I do have them. txtAmt1 through txtAmt6. they are the row end boxes in the form. 

check this:

Private Sub cmdCheckOut_Click()

'       lblDate.Caption = Format(Date, "Medium Date")

Amount1 = txtAmt1.Text

Amount2 = txtAmt2.Text

Amount3 = txtAmt3.Text

Amount4 = txtAmt4.Text

Amount5 = txtAmt5.Text

Amount6 = txtAmt6.Text

lbltotal.Caption = (Amount1 + Amount2 + Amount3 + Amount4 + Amount5 + Amount6)

lbltotal.Caption = Format(lbltotal.Caption, "$#,##0.00")

please use this link to reach the design https://1drv.ms/x/s!AlF293aweqwLkU41VGBJamIWWhfR

 i just hope i understood well. am yet to fire up excel and work with your code. will get back to you soon with any results,  thank you and everyone so much

Answer 4

First off, you don't have textboxes named txtAmt? - but you do have txtQty?, so change your code to

Private Sub cmdCheckOut_Click()

    On Error Resume Next

    Amount1 = txtPrice1.Text * txtQty1.Text

    Amount2 = txtPrice2.Text * txtQty2.Text

    Amount3 = txtPrice3.Text * txtQty3.Text

    Amount4 = txtPrice4.Text * txtQty4.Text

    Amount5 = txtPrice5.Text * txtQty5.Text

    Amount6 = txtPrice6.Text * txtQty6.Text

    lbltotal.Caption = Format(Amount1 + Amount2 + Amount3 + Amount4 + Amount5 + Amount6, "$#,##0.00")

End Sub

Answer 5

Create a new class module, name it "MyClass" and paste in this code

--- schnipp ---

Option Explicit

Public Parent As nizamForm

Public WithEvents txtPrice As MSForms.TextBox

Public WithEvents txtQty As MSForms.TextBox

Public txtAmt As MSForms.TextBox

Private Sub txtPrice_Change()

  On Error Resume Next

  'Assume failure

  txtAmt = ""

  'Do the calculation

  txtAmt = CDbl(txtPrice) * CDbl(txtQty)

  'Update total

  Parent.UpdateTotal

End Sub

Private Sub txtQty_Change()

  'Same

  txtPrice_Change

End Sub

--- schnapp ---

Replace all code in your userform with the code below.

Andreas.

Option Explicit

Dim MyEvents As New Collection

Public Sub UpdateTotal()

  Dim Result As Double

  Dim i As Integer

  For i = 1 To 6

    If IsNumeric(Me.Controls("txtAmt" & i)) Then

      Result = Result + CDbl(Me.Controls("txtAmt" & i))

    End If

  Next

  Me.lbltotal = Format(Result, "0.00")

End Sub

Private Sub UserForm_Initialize()

  Dim i As Integer

  Dim This As MyClass

  For i = 1 To 6

    'Create a new handler

    Set This = New MyClass

    With This

      Set .Parent = Me

      Set .txtPrice = Me.Controls("txtPrice" & i)

      Set .txtQty = Me.Controls("txtQty" & i)

      Set .txtAmt = Me.Controls("txtAmt" & i)

    End With

    'Keep it alive

    MyEvents.Add This

  Next

End Sub