VBA Macro Walkthrough
One of the best ways of learning VBA is to take a look at completely functioning macros & code to see how others approach a problem. In this video lesson, I walk you through a Purchase Order Generator that I created using Excel and VBA. Throughout the video, the following topics are discussed:
- How to create unique userforms which allow users to input information in a separate window.
- Usage of VBA userform toolbox items such as Label, ListBox, TextBox, and Buttons.
- Coding related to specific events (when an item in a list is chosen, or a button is clicked).
- Declaring variables, and using worksheet functions in VBA.
- Populating Purchase Order sheet with relevant product information based on user’s selections.
Download the Purchase Order Generator workbook to follow along.
Also, this is the first video lesson with HOMEWORK!! Please feel free to complete the assignments in the worksheet and send to ben@excelexposure.com for feedback.
See the Guest Video Lesson of the Homework Solution here!
Really great stuff Ben.
Always seem to be accessing your tutorials when I’m in need of an excel booster.
It seems you always have a cold though??
Would there be any reason why the list in the product selection macro doesn’t appear in Mac 2011? Im just trying to correct this to progress through the lesson and cannot seem to figure it out…
Why aren’t the videos working
Hi,
They seem to be working fine on my end. Maybe Vimeo was down for maintenance or had a temporary hiccup?
If you continue to have problems let me know.
Ben
Hi
In AddItem Sub you could use an error trap. If you by mistake forget to enter a value for quantity then program exits with a bug. An On Error Resume next could be the solution but again if something else happens it wont help. I suggest catching the error with an if statement, ie:
If ProductList.Value = “” Then QuantityBox.SetFocus
followed by a msgbox informing the user and all wrapped in a loop until the user enters a value
George
Thanks, George.
At some point I’ll do a lesson on error handling in VBA. Even I need to refresh my memory on the best usages of them. I find myself using the following link whenever I’m confronted with Error Handling problems:
http://www.cpearson.com/excel/errorhandling.htm
Thanks for the suggestion!
Ben
Yes, cpearson.com is a great site. A video on error handling will help. I’ll go through your practice file and send you my proposal for your homework
Thanks, sounds great!
How did you populate the list in the product selection menu?
I’ve marked up a relevant screenshot here which should help explain:
http://i.imgur.com/wqVQ1.png
If you go into the VBA Userform area, click on the ProductSelection Userform, you’ll see in the properties menu an area for ‘RowSource’. Here I’ve put the name of the range in the spreadsheet that refers to the ProductNames as highlighted in the picture.
I’ve done this using a named range, which I have another lesson on if you’re interested. In this example instead of A6:A25 it is known as the range ‘ProductNames’ which you can see in the name box on the right side of the screenshot.
Hope that helps!
Thanks for the tutorial, Ben!