Solved

How do I autofill an Excel user form control text box when another control on the form is input ?

Posted on 2013-10-26
9
1,893 Views
Last Modified: 2013-10-27
Hi Experts,

I have an Excel 2010 user form with a number of fields (text box controls) representing sales order lines (please see attached screenshot).

When I enter a figure in the Quantity (Qty) or the Unit Price field I want the Net field to autopopulate with the result of the calculation of Qty * Unit Price to two decimal figures.

What's the VBA code I'd need for this event ?

Thanks in anticipation
Toco
Sales-Order-screenshot.jpg
0
Comment
Question by:Tocogroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 14

Expert Comment

by:Faustulus
ID: 39602769
In the code sheet of the Userform, create a private function, say,
Private Function LineTotal() As String
    LineTotal = Format(Qty * UnitPrice, "#,##0.00")
End Function
where 'Qty' and 'UnitPrice' are the Value properties of the two appropriate text boxes in your form.
Now, create an event procedure for the Change event for each of the text boxes referenced in the function. In each of these you should have this code,
    NetField.Text = LineTotal
where 'NetField' is the TextBox where you wish the total to appear.
0
 

Author Comment

by:Tocogroup
ID: 39602833
I'm struggling to get my head round this bit.

Below is a cut-down version of what I currently have coded. The Net field is only updated when the user clicks the UPDATE button.

 Private Sub cbUpdate_Click()
         curQty = Me.Controls("teQty" & i)
         curUnitPrice = Me.Controls("teUnitPrice" & i)
         curNet = curQty * curUnitPrice
         SalesItemsArray(ActiveSalesItem, 5) = curNet
         Me.Controls("teNet" & i).Value = Format(Me.Controls("teNet" & i).Value, "#,##0.00")
End Sub

This is why I think an autopopulate would be more user-friendly but I'm not sure how the code above converts into using an event procedure and a function with parameters. What does the event procedure look like ?

Will I have to pass the curQty and curUnitPrice to the function as parameters ?
What happens if the user enters only a Qty ? Although I guess I can test for that in the function.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39602910
"Events" are things that happen. In your Sub cbUpdate_Click procedure you are, in fact, capturing the Click even of the control cbUpdate. Other events include Entry and Exit, Change and others. All event procedures are set up by VBA. You don't need to pass any parameters. In fact, you can't.
In the VBE window displaying the code sheet of your Userform you will see two drop-downs along the opper edge. One says (General), the other (Declarations). Drop down the left one (General) and you will see a list of all your controls. Select the control you are interested in. Now, when you open the right drop-down you will see all the events available for that particular control. When you click on one VBA will insert the correct procedure into your code sheet. That procedure will run whenever the event occurs.
Be careful not to trigger events as a chain reaction of your code. You can plan on letting VBA trigger events that are generated by your code. More often this isn't desired. So, you turn off events while your code is running event triggers and turn them back on when you are done.
Application.EnableEvents = False
Application.EnableEvents = True
The following is the thought in your code which I suggested to put in the function LineTotal() earlier this evening.
         curQty = Me.Controls("teQty" & i)
         curUnitPrice = Me.Controls("teUnitPrice" & i)
         curNet = curQty * curUnitPrice
         Me.Controls("teNet" & i).Value = Format(curNet, "#,##0.00")
Actually, you can do it shorter, as I already suggested:-
         Me.Controls("teNet" & i).Value = Format(Me.Controls("teQty" & i).Value * Me.Controls("teUnitPrice" & i), "#,##0.00")
If you hitch this to the Change events of both "teQty" & i and "teUnitPrice" & i you can delete the Update button because updating will take place automatically.
SalesItemsArray(ActiveSalesItem, 5) shouldn't be updated until you are done with the form. There should be an OK button. When you click that button the results from the form are carried into the array. In the Click event procedure for CmdOK you might have this line,
SalesItemsArray(ActiveSalesItem, 5) = Me.Controls("teNet" & i).Value
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Tocogroup
ID: 39602977
Well that's really useful to know. I didn't realise clicking on the control dropdown list at the top generates the procedure for the selected event. Excellent ! Thanks for that gem.

Actually, the Update button in my application updates the array (I gave you a much reduced code sample) from the form controls, so it has the same effect as what you are proposing with an OK button.

As for the event proc for the Qty field I've got this code:

Private Sub teQty1_Change()

i = 1   ' this was just so I could get the first field of the form to work for testing purposes
   
   Me.Controls("teNet" & i).Value = Format(Me.Controls("teQty" & i).Value * Me.Controls("teUnitPrice" & i), "#,##0.00")

End Sub

But I'm getting a 'Run time error 13 Type mismatch' error against the above code. I've tried a few things (including .Text rather than .Value) but can't get it to work.

I guess I'll have to create an event procedure for each of the 7 Qty textbox controls ?

Toco
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 39603351
The Format function requires a numeric value. If the addressed TextBox is blank the error #13 will result. Going back to your original design where you assigned the value to a variable first would enable you to check that each of these actually has a value other than zero, but that isn't the real issue here. What you really need is to decide what to show in teNet if you can't show a valid total. So, this is what I suggest,
Private Sub teQty1_Change()
    
    Dim i As Long       ' I urge you to use Option Explicit at the top of your sheet

    i = 1   ' this was just so I could get the first field of the form to work for testing purposes
    
   On Error Resume Next
   Me.Controls("teNet" & i).Value = Format(Me.Controls("teQty" & i).Value * Me.Controls("teUnitPrice" & i), "#,##0.00")
    If Err Then
        Me.Controls("teNet" & i).Value = ""
    End If
End Sub

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39603525
That works great. I've applied the same logic to the Unit Price text box too, as this is a value which may change subsequently.

I see that performing an arithmetic calculation on a null field will result in an error so this is a nice get around.

So finally, if I had a user form table of say, 30 rows (with 30 occurrences of Qty) then would I have to have 30 instances of your procedure in my code ?

Or is there a way of testing for a change in any one of a group of text boxes but by coding it in a single procedure (rather than 30) ? If the solution is anything than simple then I'll complete this post, award the points and then raise another question.
0
 

Author Comment

by:Tocogroup
ID: 39603661
One other thing, I copied your statement for calculating the VAT and applied it to the Gross field ......

   Me.Controls("teGross1").Value = Format(Me.Controls("teNet1").Value + Me.Controls("teVAT1"), "#,##0.00")

All it did though was concatenate the Net value with the VAT value, for example, 400.0080.00
Yet, if I replace the + sign with a - sign, it subtracts the figures correctly.

So I changed the code to this....

   Me.Controls("teGross1").Value = Format(Val(Me.Controls("teNet1").Value) + Val(Me.Controls("teVAT1")), "#,##0.00")
 
and it calculated the Gross amount correctly. Don't understand this at all  :-)
0
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39603824
It would appear that your VAT is a negative value. So, 10 +(-1) = 9. You have to add a negative value in order to subtract it.
Reminds me of what my mother said about T-shirts for her grand children. She said if it's to large it fits and if it fits it's too small. Make sense?
0
 

Author Closing Comment

by:Tocogroup
ID: 39603845
Many thanks for your help. I've picked up a number of tips from the one original question.
Much appreciated.
Toco
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question