Link to home
Start Free TrialLog in
Avatar of Tocogroup
TocogroupFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
Avatar of Faustulus
Faustulus
Flag of Singapore image

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.
Avatar of Tocogroup

ASKER

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.
"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
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
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

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.
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  :-)
ASKER CERTIFIED SOLUTION
Avatar of Faustulus
Faustulus
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many thanks for your help. I've picked up a number of tips from the one original question.
Much appreciated.
Toco