Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Newbie - needs help with recalculation

  Asked By: Arland    Date: Aug 22    Category: MS Office    Views: 999
  

I have a userform that uses vlookup to populate description and unit
price. I have QTY and Total Price columns. When I change the quantity
I would like the total price to be recalculated, but currently the only
way it will recalculate is if I re-scan a barcode. Any ideas?

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Amanda Carter     Answered On: Aug 22

It's hard to know without seeing how the formula are setup, but to
start with I would check the calculation under the "Tools - Options".
It may have been set to Manual...

Otherwise, you could try put this sub under the ThisWorkBook to force
it to calculate everytime you change  something.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Calculate
End Sub

 
Answer #2    Answered By: Sallie Hill     Answered On: Aug 22

Does the total  price cell contain a formula or is it just a value put there by
a Macro?

Go to Tools/Options and on the Calculation tab check that automatic is
selected.

 
Answer #3    Answered By: Abel Fischer     Answered On: Aug 22

Total Price is a formula.

I have QTY - Barcode - Description - Unit Price - Total Price and then a
Grand Total. QTY and Barcode are input manually - all others are
formulas.

 
Answer #4    Answered By: Julia Silva     Answered On: Aug 22

Is automatic recalculation turned on?

When you alter a QTY what words appear in the status bar (bottom of the Excel
window)? Ready, Calculating, Calculate?


Just to confirm one point. You say that QTY and barcode  are entered manually.
You also say "Rescan a barcode" Is QTY entered at the keyboard and barcode by
scanning?

Are there any macros associated with this workbook? If so do any of those
interfere with what is happening? Is there a macro on the change  event? Is
there a macro which controls the barcode scanning? If so please post the code
so we can understand it ( You will need to copy the code and paste it into the
email as attachments are not allowed on this list.)

 
Answer #5    Answered By: Grant Jones     Answered On: Aug 22

I'm using a UserForm for all of this - with textboxes for
everything. I tried using a sub routine for on the change  event, but
that gives me an error when the qty is added for the first time without
barcode  to fill in the Unit Price.

I have 8 lines on my invoice - I would like to have them added
dynamically, however, I am unsure how to do that. This is the code for
my first line in the invoice. The remaining lines are 2 - 8. I tried
using Application.Volatile (True), but that doesn't seem to change
anything.

Private Sub txtBarCode1_AfterUpdate()



Application.Volatile (True)



If IsNumeric(txtBarCode1) Then



Desc1.Text = Application.VLookup(Val(txtBarCode1.Text), _

Worksheets("Product List").Range("A:E"), 3, 0)



intUP1.Text = Format(Application.VLookup(Val(txtBarCode1.Text), _

Worksheets("Product List").Range("A:E"), 4, 0), "#,##0.00")



intTotal1.Text =
Format(Application.WorksheetFunction.Product(intQTY1, intUP1),
"#,##0.00")

txtBarCode2.SetFocus



Else



Desc1.Text = Application.VLookup(txtBarCode1.Text, _

Worksheets("Product List").Range("A:E"), 3, 0)



intUP1.Text = Format(Application.VLookup(txtBarCode1.Text, _

Worksheets("Product List").Range("A:E"), 4, 0), "#,##0.00")



intTotal1.Text =
Format(Application.WorksheetFunction.Product(intQTY1, intUP1),
"#,##0.00")



txtBarCode2.SetFocus



End If



If IsNull(intTotal1) Then

intQTY2 = ""

Else

intQTY2 = "1"

End If



Call RunningTotal



End Sub

 
Answer #6    Answered By: Phailin Jainukul     Answered On: Aug 22

A heap of issues. Trouble is I am quite busy right now. I will try to
get to it later today. Of course if anyone else wants to chip in that would
be great.

 
Answer #7    Answered By: Bonita Garcia     Answered On: Aug 22

You probably solved this ages ago, but here goes:

Put in your code for the change  event at the beginning
Something like

If txtBarcode1.value is Null then
Exit Sub
End If

Than follow it with the

intTotal1.Text = Format(Application.WorksheetFunction.Product(intQTY1,
intUP1), "#,##0.00")


*** Not tested on animals (or anything else) but it is approximately right.

You may need to change the condition of txtBarcode1.value depending on what it
gets initialised to. It may need to be ="" or =0

The dynamic userform  is more complex. I will have a go when I get time. You
would need to add controls to the userform and redisplay it.

What would the trigger be for a new row?

I can't help  thinking life would be easier if you used Excel to do the stuff
it is good at like calculating, etc and just used the userform for input.

 
Answer #8    Answered By: Matt William     Answered On: Sep 13

what do you mean by saying "if you re-scan a barcode"?
For scan barcode in VB, see this:
www.onbarcode.com/.../vb-net-barcode-reader.html

 
Didn't find what you were looking for? Find more on VBA Newbie - needs help with recalculation Or get search suggestion and latest updates.




Tagged: