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!

This entry was posted in VBA / Macros and tagged , , , , , , . Bookmark the permalink.

19 Responses to VBA Walkthrough #1: Purchase Order Generator

  1. Nicka says:

    Hi i just needed help, can you teach me how can i copy the PO i have generated to another worksheet without erasing the previous entry, (beginner homework number 2), i’ve already accomplished the copying part but somehow it overwrites the previous copied PO, what codes should i use in order to copy the new generated PO under the previous ones.

    here’s the code that i used

    Sub ButtonToCopy()

    ‘ ButtonToCopy Macro


    Range(“B2:N23”).Select
    Selection.Copy
    Sheets(“Sheet4”).Select
    Range(“A2:M23”).Select
    ‘I need to put here something like the “rowstart” to adjust the copied PO so it will not overwrite the previous ones

    ActiveSheet.Paste
    End Sub

    Thanks 😀

  2. Karamveer says:

    It’s very halpfull.

  3. Anonymous says:

    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.

  4. Colm says:

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

  5. tylerjkerr says:

    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…

    • Justin says:

      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

      • Josh says:

        I found that that didn’t work for me on my mac, however modifying it slightly did:
        (Just copy and past this into the top of the Product Selection form)

        Private Sub Userform_initialize()
        ProductList.List = Range(“ProductListing”).Value
        End Sub

        Hope this helps someone.

  6. Anonymous says:

    Why aren’t the videos working

  7. Anonymous says:

    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

  8. rrrobins says:

    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!

      • Roy Cox says:

        Using an ordinary named range like this means that the list is fixed and to add or remove products means the code needs amending constantly. Much better to use a Dynamic Named Range or as you are using Excel 2007 or later convert the list to a Table. Also, the list property is a better way to populate a listbox

    • Anonymous says:

      Thanks for the tutorial, Ben!

Leave a Reply