Total From Spin Button

[adam]

Hi,

I’m having the following spin button code in my user form

Code: Select all

Private Sub SpinButton2_SpinDown()
txtQty.Value = Format(0, "0.00")
txtQty.Value = txtQty.Value + SpinButton2.Value
txtQty.Value = Format(txtQty.Value, "0")
End Sub
Private Sub SpinButton2_SpinUp()
txtQty.Value = Format(0, "0.00")
txtQty.Value = txtQty.Value + SpinButton2.Value
txtQty.Value = Format(txtQty.Value, "0")
End Sub

My intention of the spin button is to increase or decrease the quantity without having the user to type the numbers in the text box txtQty.
The value that should reside in the text box txtTotal is the calculated (multiplied) value from the text box txtRate & txtQty.

What I’m trying to get help is how to change the value on textbox txtTotal(with the help of the spin button) when the quantity gets changed with rate.

Any help would be kindly appreciated.

For example the value on the text box txtrate is 10.00 and quantity is 1. If I change the quantity from 1 to 2 I want the value in txtTotal to be 20.00

[HansV]

Add the following line to both SpinButton2_SpinDown and SpinButton2_SpinUp, above End Sub:

Me.txtTotal = Me.txtRate * Me.txtQty

By the way, do you really want to add the value of the spin button to the quantity? This means that the first time the user clicks the "up" button, 1 is added, the second time 2, the third time 3 etc. So after 3 clicks, the value has increased by 1+2+3 = 6.

I'd use

txtQty.Value = txtQty.Value - 1

in the SpinDown code, and

txtQty.Value = txtQty.Value + 1

in the SpinUp code. But the choice is up to you.

[adam]

Hans, Is this how you preferred?

Code: Select all

Private Sub SpinButton2_SpinDown()
txtQty.Value = Format(0, "0.00")
txtQty.Value = txtQty.Value - 1
txtQty.Value = Format(txtQty.Value, "0")
Me.txtTotal = Me.txtRate * Me.txtQty
End Sub
Private Sub SpinButton2_SpinUp()
txtQty.Value = Format(0, "0.00")
txtQty.Value = txtQty.Value + 1
txtQty.Value = Format(txtQty.Value, "0")
Me.txtTotal = Me.txtRate * Me.txtQty
End Sub

[HansV]

I'd omit the lines

txtQty.Value = Format(0, "0.00")

[adam]

Thanks Hans. It works much better now.

[adam]

With the guidelines given in the Post=19815 I have added a combo box which changes the rate in textbox txtRate to either price such as Normal or Discount.

Supposing If I write the rate as 10.00 and the quantity as 1 which gives a total of 10.00
When I change the rate from the combo box with the normal price as 20.00 and quantity as 1 the total does not get changed as 20.00. How may I overcome this?

[HansV]

Use the Change or AfterUpdate event of the combo box to fill txtTotal.

[adam]

Thanks. It worked.

[adam]

Once again according to the suggestion in Post=19815, the following v look up procedure has been created where when the user writes the code in the textbox txtCode, the rest gets filled. But with the addition of the lines
Me.txtTotal = Me.txtRate * Me.txtQty
the total does not seem to get on the txtTotal even when the number is changed in the quantity in the textbox txtQty

What may be the reason for this?

Code: Select all

Private Sub txtCode_AfterUpdate()
  Dim lngCol As Long
  Dim rng As Range
   If Me.cboPriceType = "Discount" Then
      lngCol = 3
    Else
      lngCol = 4
    End If
  If Me.txtCode = "" Then
    Me.txtDescription = ""
      Me.txtCategory = ""
     Me.txtTotal = Me.txtRate * Me.txtQty
  Else
    Set rng = Worksheets("Products").Range("A6:E1048576")
    Me.txtDescription = Application.VLookup(Val(Me.txtCode), rng, 2, False)
      Me.txtCategory = Application.VLookup(Val(Me.txtCode), rng, 5, False)
      
  End If
    Me.txtRate = Application.VLookup(Val(Me.txtCode), rng, lngCol, False)
   
End Sub

[HansV]

You have to update txtTotal from each of the controls (text boxes, combo boxes) that contribute to the value of txtTotal. So if you wish to update txtTotal when the user edits txtQty, you must write code for the AfterUpdate event of txtQty.

[adam]

To understand my problem better I have attached a sample workbook.

In the attached; when the user writes the code in the text box txtcode, the code behind the form fills the appropriate text boxes with the corresponding values form the worksheet.

The quantity is written manually which automatically changes the total.

My question of concern is that,
Suppose if I write the code 0001 in the textbox txtcode and write quantity as 1 the total gets 40.00 if I select normal price from combo box.

Having this, if I change the code as 0002 all the textboxes except the txtQty & txtTotal gets changed. But the txtTotal does not get changed to 20.00 even if the txtQty is 1.

How could I overcome this?

[HansV]

Sprinkling the line

Me.txtTotal = Me.txtRate * Me.txtQty

4 times through the txtCode_AfterUpdate procedure won't help, especially not if you place those lines BEFORE you look up txtRate. You should calculate the total just once AFTER you have looked up txtRate.

And why have you changed the line

If Me.cboPriceType = "Discount" Then

to

If Me.cboPriceType = "Normal" Then

This means that you switch the normal and discount prices.

[adam]

Changing the line from "Discount" to "normal" was a mistake and I'm sorry for that. By the way did you mean the code should be as follows?!

Code: Select all

Private Sub txtCode_AfterUpdate()
  Dim lngCol As Long
  Dim rng As Range
   If Me.cboPriceType = "Discount" Then
      lngCol = 3
    Else
      lngCol = 4
    End If
  If Me.txtCode = "" Then
    Me.txtDescription = ""
      Me.txtCategory = ""
    
  Else
    Set rng = Worksheets("Products").Range("A6:E1048576")
    Me.txtDescription = Application.VLookup(Val(Me.txtCode), rng, 2, False)
      Me.txtCategory = Application.VLookup(Val(Me.txtCode), rng, 5, False)
  End If
  Me.txtCode = Format(Me.txtCode, "0000")
    Me.txtRate = Application.VLookup(Val(Me.txtCode), rng, lngCol, False)
   Me.txtTotal = Me.txtRate * Me.txtQty
End Sub

[HansV]

The line that sets txtTotal is placed correctly now.
The line that sets txtRate should be above End If, for it uses the range rng that you set between Else and End If.

[adam]

But in doing as mentioned above I'm getting the debug message highlighting the line Me.txtTotal = Me.txtRate * Me.txtQty
Here's the current code

Code: Select all

Private Sub txtCode_AfterUpdate()
  Dim lngCol As Long
  Dim rng As Range
   If Me.cboPriceType = "Discount" Then
      lngCol = 3
    Else
      lngCol = 4
    End If
  If Me.txtCode = "" Then
    Me.txtDescription = ""
      Me.txtCategory = ""
    
  Else
    Set rng = Worksheets("Products").Range("A6:E1048576")
    Me.txtDescription = Application.VLookup(Val(Me.txtCode), rng, 2, False)
      Me.txtCategory = Application.VLookup(Val(Me.txtCode), rng, 5, False)
    Me.txtRate = Application.VLookup(Val(Me.txtCode), rng, lngCol, False)
  End If
  Me.txtCode = Format(Me.txtCode, "0000")
   Me.txtTotal = Me.txtRate * Me.txtQty
   
End Sub

[HansV]

Does it work better if you use

Me.txtTotal = Val(Me.txtRate) * Val(Me.txtQty)

[adam]

Thanks Hans. It worked finally.