VBA Training Series – How to Create a Userform (Part 3 of 3)

Macros & VBA Training Series Part 3:

How to Create a Userform to Export Worksheets

Double-click video to view in Full Screen HD.

Download the file used in this video: 

The Exports Sheets Userform.zip

Bonus: How to Export The Selected Items Only

In this video I explain how to set the listbox multiselect property to allow the user to select multiple items. We then add code to the Export button’s Click event to only export the selected sheets. We do this with an IF statement.

Macros & VBA Training Series

Video 1

Intro to VBA:
Writing Your First Macro

Video 2

Automating Tasks:
The Summary Report

Video 3

Userforms:
The Exports Sheets App

How features can we add to the userform?

Please leave a comment below with your answer, and any questions.  Thanks!

147 comments

1 2 3

Your email address will not be published. Required fields are marked *

  • Nothing more, nothing less – a very good movie, construction UserForm explained in a simple and understandable. Now it’s time to apply the knowledge learned.

    Reply

  • Thanks for watching! I will be sending you an email with more info on the VBA Pro Course in just a few days.

    If you received the link to this page from a friend, you can signup here to get notified about the rest of the series.

    Please leave a comment below with any questions. Thanks again and have a great day!

    Reply

  • Hi Jon,

    I was able to follow and keep up with video 2. That is without seeing video 1 which I missed. Video 3 which is the one I’m most interested in you lost me……………………..

    Reply

  • Hi Jon,
    Good addition with the bonus video, it makes sense to have the form if there is the option to select the sheets to save. Otherwise a one button macro can save all the sheets with no options.
    I really enjoyed the videos, now I feel confident using forms instead of form controls on my worksheets.
    Thanks,
    Pablo

    Reply

  • Dear Jon,
    I appreciate your 3 videos on VBA. They are great and i did like them. I Thank you so much. Looking forward to becoming a VBA/ Macros pro through you.

    Reply

    • Thanks Tariq! I tried to cover a lot in these videos to show the possibilities with VBA. I realize they might be a little fast. My VBA Pro Course goes into a lot more detail on the introductory topics. There are four modules in the course dedicated to getting started with VBA and learning all the basic concepts. This will give you a great foundation for more advanced coding and application development, which I cover in the second half of the course. I’ll be sending you more info on the course in a few days. Thanks again!

      Reply

  • All the 3 tutorials are really excellent and covers a very good example, it has evoked great interest in me to learn macro programming.

    Reply

  • I really enjoyed the videos and I can see where I can use them in my work. Question — I have sheets that have formulas in them. I know sometimes when I right click the tab and create a new copy the formulas get messed up. Will the userform method prevent that or how can I ensure that the formulas continue to work or is there a way to have the form copy them as values?

    Looking forward to the Pro course.

    thanks again for providing this informative course.

    Carole T.

    Reply

    • Thanks Carole! And thanks for joining the course!

      This is a great question. There are a few different ways to do it. Probably the easiest is to break the links to the original workbook that you are exporting from. This would replace the formula links to the original workbook with values. To do this manually you would go to the Data tab on the Ribbon > Edit Links > Break Link.

      You can also do this in VBA with one line of code. After the Activeworkbook.SaveAs line in the code, add the following line of code.

      ActiveWorkbook.BreakLink Name:=wb.Name, Type:=xlExcelLinks

      The BreakLink method breaks the links of the workbook name you specify in the Name parameter.

      You will also want to save the workbooks before closing, so change the Close method argument to True.
      ActiveWorkbook.Close True

      Please let me know if you have any questions. Thanks again Carole!

      Reply

  • Hi,

    Viewing your 3 video gave me a good incentive to learn more about VBA programming. Can we accomplish the same program by recording the moves in a macro?

    I heard that having files on my disk that contain macros is risky. Is it a concern and if so, what can we do to protect our computer? This is the main reason why I stayed away from VBA and the macros.

    Reply

    • Thanks Yvan! I happy to hear you are learning VBA. The macro recorder will produce code based on your moves and actions in Excel. However, it does not produce the most efficient code. For example, it will not create a loop like we created in the video. Having files that contain macros you wrote is not risky. You will run into potential risks when you download macro enabled workbooks from the internet from unknown sources. Or copy code from an unknown source and run it before reading through all the code first. Please let me know if you have any other questions.

      Reply

  • Thank you a lot for the lesson, really like them.
    I have one important task at work and it takes long time which is coloring the charts bar every time,

    Her is the macro that I found while brosing and I need to modify it. This macro color all chart bars with the first cell from the series of the chart.
    And it is only for the active sheet. My goal is to modify it. So, I can get every bar in the chart colored in all my worksheet not only the active sheet.
    I will really appreciate if you can help me on this as I am just a bigginer and I don’t know that much about VBA.

    Sub CellColorsToChart()
    Dim oChart As ChartObject
    Dim MySeries As Series
    Dim FormulaSplit As Variant
    Dim SourceRange As Range
    Dim SourceRangeColor As Long

    ‘Loop through all charts in the active sheet
    For Each oChart In ActiveSheet.ChartObjects

    ‘Loop through all series in the target chart
    For Each MySeries In oChart.Chart.SeriesCollection

    ‘Get Source Data Range for the target series
    FormulaSplit = Split(MySeries.Formula, “,”)

    ‘Capture the first cell in the source range then trap the color
    Set SourceRange = Range(FormulaSplit(2)).Item(1)
    SourceRangeColor = SourceRange.Interior.Color

    On Error Resume Next
    ‘Coloring for Excel 2003
    MySeries.Interior.Color = SourceRangeColor
    MySeries.Border.Color = SourceRangeColor
    MySeries.MarkerBackgroundColorIndex = SourceRangeColor
    MySeries.MarkerForegroundColorIndex = SourceRangeColor

    ‘Coloring for Excel 2007 and 2010
    MySeries.MarkerBackgroundColor = SourceRangeColor
    MySeries.MarkerForegroundColor = SourceRangeColor
    MySeries.Format.Line.ForeColor.RGB = SourceRangeColor
    MySeries.Format.Line.BackColor.RGB = SourceRangeColor
    MySeries.Format.Fill.ForeColor.RGB = SourceRangeColor
    MySeries.Format.Line.ForeColor.RGB = SourceRangeColor

    Next MySeries
    Next oChart

    End Sub

    Reply

  • Hi Jon!
    Great tutorial!
    I like the 3rd video the most. You have explained how to use form control very well.
    Thank you!

    Reply

  • Great video! I have been writing VBA based on the object model and code snippets from the web but it is much easier when a Pro shows you how I all comes together in a logical way. I learned a lot, thanks Jon.

    Reply

  • Thank you for the bonus clip!

    I look forward to trying out some more advanced VBA scripting to automate tasks, like accepting user input as variables in modelling scenarios. I did find it interesting that the order of the subroutines did not appear to matter. I would have thought that the userform initialization (worksheet list) was required before the command button lines could be run to save the sheets to individual files.

    Thanks again.

    Reply

  • when you showed the Saveas command it had parenthesis around the parameters. Obviously they are not necessary. Would the code still work if you used them? you might want to acknowledge the presence of the parentheses in your talk.
    Good clear code and presentation… I will be using this!

    Reply

  • Hello JON. Please keep up your awesome work! I am a very very beginner brazilian VBA student and I am loving your way of teaching and show the things! 🙂

    I will keeping follow you for sure!

    Reply

  • Hi Jon
    Many thanks for this excellent video series, I’ve rarely gone beyond editing recorded macros, but you’ve inspired me to do so much more!

    Reply

  • To me, a beginner to VBA, the three videos were excellent illustrations of the usefulness of macros in Excel. I have clicked the link on your resources page and purchased John Walkenbach’s book. I hope you pick up some commission on the purchase Jon.
    I look forward to your newsletters.

    Reply

  • Huge thanks for these 3 fantastic videos, Jon!! Really well presented and a good intro to VBA 🙂

    I have a Mac and am having trouble with the file path for the saved files – I don’t think the Mac likes the spaces that VBA insists on putting in to look like this:
    “Users / Ella / Desktop / Exports / ” & sSht & “.xlsx”, FileFormat:=51
    I type it in without spaces…. I’ve also tried using colons (suggestion from the web)

    Please can you advise?

    Reply

  • hello, I just enrolled in this course. I use excel 2010, it is okay for this course? also, when I was watching your free VBA course, I found there is no ” Multiselect” in properties when I wanted to choose more items in list box to export two or more sheets to create individual sheets. Is it because of my excel version? or any other reasons? Thanks. looking forward your reply.

    Reply

    • Hi Ning,

      Thanks for enrolling in the VBA Pro Course!

      Yes, Excel 2010 is a great version to use. It’s probably my favorite version.

      In regards to the multiselect property, you will need to first select the listbox by left clicking it, then you should see the listbox properties in the properties window.

      If you still don’t see it, send me a screenshot of your VB Editor.

      Thanks again Ning!

      Reply

  • Thanks for a great tutorial. I have just one question: how do you prevent the Summary sheet from being included in your list and therefore exported? Or does the export have to happen before you would do all the things in video #2?

    Reply

    • Hi Ruth,
      Great question. There are a few ways to go about it. One way is to check if the current sheet in the loop is the Summary sheet. If it is then skip it and do NOT add it to the listbox.

      We can do this by adding an IF statement to the Initialize macro.

          For lSht = 1 To Worksheets.Count
        If Worksheets(lSht).Name <> "Summary" Then
            Me.ListBox_Sheets.AddItem Worksheets(lSht).Name
        End If
    Next lSht

      I added the If statement to the code above. This basically says that if the current sheet name is NOT “<>” Summary then add the sheet name to the listbox. If the current sheet name is Summary then it will just skip all lines of code between the If line and the End If line.

      Let me know if you have any questions. Thanks!

      Reply

  • Impressive presentation (good screens and explanations). But in the first two videos you were working on a Summary Sheet, which disappeared by the third episode. If this workbook was to be re-used on a recurring basis, how did you deal with this – hide the Summary and re-open later?

    Reply

    • Hi Wendy,
      Great question. I just posted a similar reply to Ruth that contains the code that prevents the summary sheet from being added to the listbox for the export. Check it out and let me know if you have any questions. Thanks again Wendy! 🙂

      Reply

      • Perfect – thanks! I’d like to learn more about the syntax of this stuff, declaring variables, etc. For a non-programmer, that would be helpful. When I learned that Excel formulas took arguments, for instance, I had a better notion of creating them.

        You’ve helped me a lot with your videos and I’m very appreciative!

        Reply

  • Thank you for these very easy to follow tutorials. I’ve been searching the web for sometime now as to how export data from a userform. I have one quick question, is there an easy way to have the file sent via email and then saved direct into a folder.

    Cheers
    Darran

    Reply

    • Thank you Darran! Do you want the recipient of the email to have it saved directly into a folder on their computer?

      Reply

      • Hi John,

        Yes I want it to be stored on the recipients computer as the aim is for that file to be imported into a master database so the records update.

        Any pointers you can provide are greatly appreciated.

        Thanks
        Darran

        Reply

  • Thanks for such a powerful video. Andy also requesting you to please assist about export/import excel data by using power query.

    Reply

  • Hi Sir
    Very Good Explain .Really Love this and Looking forward to learn more and more Excel VBA From Begin to Advance Level .
    I also would like to work as a freelancer . Hope after learn all it will help me to find job as a free lance

    Thanks

    Reply

    • Thanks again Kapil! VBA skills are in high demand by a lot of companies all over the world. I think you will be able to find freelance work as a VBA developer with the right skill set.

      Reply

  • Hi Jon,

    So much great work,

    just i want to know that if i have data in 1 sheet there is five name of product so how can i split sheet in different different file.

    Reply

    • Thanks Tekchand! That will depend on how your data is setup in the source sheet. If it is in a table or list, then you could use a Loop that would:

      1. Filter the data for each product.
      2. Copy the visible cells of the data range.
      3. Paste to a new workbook.
      4. Save the new workbook with a file name that includes the product name.

      There are many ways to go about this, but that should get you started. You can use the macro recorded to get the code for the filtering and creating and saving a new workbook.

      I also have a video series on how to copy and paste with VBA. Most of this is also covered in my VBA Pro Course. I hope that helps. Thanks!

      Reply

  • The video was very interesting and enjoyed working along with the download accompanying file. Had some problems and had to make to go back through the video. The download file had the if and end if that was not explained during the video. I had to clear out the VBA to eliminate the if and end if statements in order to code the VBA along with the steps given in the video in order to have the click export to work properly.

    Reply

  • Jon
    I loved this series of training video. Can I also write code to email the exported workbooks to the appropriate managers.

    Reply

    • Hi Brenda,
      Yes absolutely! Ron de Bruin has some great articles and resources on how to email workbooks from Excel. He has written a lot of the code already, so you can copy/paste most of it and adapt to your project. I hope that helps. Thanks again! 🙂

      Reply

  • Jon,
    Thank you so much for this series of training video, I loved them! “User forms” something new to me, looks amazing, will try it out! 🙂

    Reply

    • Thank you Julija! I am so happy to hear that. Userforms are awesome and can really make your macros look more professional. Thanks again!

      Reply

  • Hi Jon,

    Thanks for the videos and simple explanation. My colleagues are very happy for what I have done to reduce their workload.

    Thank you so much!

    But I seem to get stuck here and I need your help.

    Can you show me the best possible solution to export a group of sheets into each single workbook per loop?

    Thanks
    Nak

    Reply

    • Thanks Maheshwar! I’m happy to hear you enjoyed the videos. I sent more info on the course. Please let me know if you have any questions. Thanks again and have a good one! 🙂

      Reply

  • Thanks Jon for showing a possible use for a user form. My question is what if you wanted to select which tabs get exported? How would you change the code so each tab is listed in a check box so the user could select which ones to export?

    For my purpose, in the (Name) field it shows Sheet2 through Sheet6 but in the Name field shows different names. I would like to assign those to check boxes for the user to selectively export if the want.

    Reply

    • Awesome! Thanks Xiaoyan! I look forward to having you as a member of the course and helping you learn VBA. 🙂

      Reply

  • Hi jon,

    I’m new to all this and it is overwhelming but enlightening. I’ve enjoyed all the videos. It’s great to see that there is more to Excel. The codes are somewhat confusing. Any suggestions on how I can save certain codes to use on other materials?

    Thanks for the simple method teaching that you provide.

    Lionel

    Reply

  • Jon,
    this was a great & informative video. I always like learning new things especially when it can make my life a lot easier. I cannot wait until the Pro Course.

    Thanks,
    Tom

    Reply

    • Thanks Tom! I always like to learn new things as well. With Excel and VBA we will never run out of things to learn. The possibilities are endless. 🙂

      I look forward to having you as a member of the course. Thanks again!

      Reply