VBA Walkthrough #1: Purchase Order Generator

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!

14 thoughts on “VBA Walkthrough #1: Purchase Order Generator

  1. How the hell did you populate your Product List ListBox? I have been searching for hours for a way to populate a ListBox I added but I just can’t find a way. Also where the hell is your code for populating the ProductList ListBox…? I double clicked the userform but I can’t find it anywhere.

  2. 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??

  3. 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…

    • There are no row sources under list properties in Mac. As a result the productnames list was lost during the pc-mac translation. Type in:

      Private Sub Userform_initialize()
      ProductList.List = Worksheets(“Product Listing”).Range(“A6:A25″).Value
      End Sub

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

    • 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!

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s