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,764 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
  • 5
  • 4
9 Comments
 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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
Comment Utility
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
Comment Utility
"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
 

Author Comment

by:Tocogroup
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:Faustulus
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Many thanks for your help. I've picked up a number of tips from the one original question.
Much appreciated.
Toco
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now