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 *

  • Hello Jon,
    Wonderful explanation. Video in all respects is great.
    Request to mail me details of your wonder full learning modules.
    Thanks and regards,
    Rajan

    Reply

  • I appreciate your efforts, its very helpful on my end because I’m just a begginer and I do project monitoring and Evaluation so I’m beggining to see the importance of excel and VBA

    Reply

  • Thanks John for the Links, I’m still learning and its becoming very interesting, as I do project monitoring and evaluation in my daily work and I look after HIV – AIDS patient database so I’m working on a userform that can help enter data, store data and export data to word or PDF to report to my bosses, I’m still working on it. Every thing was done on assumptions I did not really understand the basic things about macros and VBA code deveopment but I’ve been searching all over the web to see what people have done and try applying for my self thanks alot again John.

    Reply

  • thank you for the explanation. very useful.
    i will wait for or next emails regarding the beginner to pro.
    best regards

    Reply

  • Hi Jon,
    I am very greatful and delighted of your effort and expertise in excel. I’m a beginner and benefiting greatly from your simply but very indept tutorials.

    Thanks again.

    PS: cant wait for your VBA Pro course.

    Reply

  • Hi John, I want to export data from a workbook.

    Like if i am having region data East 1 est 2….. west 2
    i want to export it in east 1 sheet… west 1 sheet..

    Also i want to learn more. what’s your fee and how can i get video.. please let me know

    Thanks
    Kapil

    Reply

    • Hi Kapil,
      Great question. That is a very common task to split data into sheets within the workbook.

      I will be sending you more info about the VBA Pro Course in just a few days. Stay tuned.

      Reply

  • I tried this on a monthly report I sent out. when I got the to ME.

    Listbox was not an option, and it keeps giving me an error. I don’t know how to get LISTBOX to show in my choices under ME.

    Any helP?

    Reply

    • Kristina,

      Did you rename your listbox object to ListBox_Sheets? I think me. refers to the userform and ListBox_Sheets has to be an object on the userform for it to appear in the drop-down.

      Jess

      Reply

  • Hi Jon, your videos are very useful and well explained: a simple and easy way to learn VBA. I’d like to have more info about the VBA Pro Course. Thanks

    Reply

    • Thank you Damiano! I really appreciate the nice feedback. I sent you an email with more info on the course. I look forward to having you join us.

      Reply

  • Jon,

    Another Great Presentation! I received an error when I ran my code but it ran fine once I made sure none of my sheets were hidden. Go figure! Anyway, thanks and awaiting your email regarding the pro series. It will be nice to use loops in vba than writing long formulas in excel.

    Jess

    Reply

    • Hi Jess,
      Yes, I think I forgot to mention that the sheets will need to be visible in this case. We can also add code to unhide and then hide sheets if the macro needs them to be visible. Not all code for a sheet requires the sheet to be visible.
      I look forward to having you join us for The VBA Pro Course. Thanks again! 🙂

      Reply

  • Thanks Jon, this is great stuff! Especially for someone who was scarred by their computer science professors in their college days. I am looking forward to the info on the Pro Course.

    Thank you!

    Ken

    Reply

  • Thanks Jon! Your lessons are presented in clear, easy to understand modules. I would recommend your training to anyone wanting to learn VBA.

    Reply

  • Really good. Please do some more series. I would love to see some more topics on automation i.e. Get and Transform data to read in spreadsheets and ways to email and save results.
    Thank you.
    Agnes

    Reply

  • Super! Excel is all about saving time and improving accuracy. These Macros showed a way to do both. I can envision a future versions of Excel having some of these functions built into Excel itself.

    Reply

  • I’m using Excel 2010…and lesson 3 doesn’t work but doesn’t give me an error. I am able to initialize the Userform but that is all.

    Is it my version of excel?

    Thank you

    Reply

  • Yours videos were clear and concise and well presented. Thanks John and I look forwards to your VBA pro course.

    Reply

  • Hello I wrote back the following in response of an email I received from you Sir Jon. I hope you will read it and respond back as quickly as possible:

    “Hello Sir Jon,

    I am very interrested about the offer and want to make 3 payements of 99$. But I have never done an online transaction before. I don’t have a mastercard or a paypal card. How can I manage to get you have the money. Because I really want to start the course at the very beginning so that I won’t miss anything. What can I do before it’s too late ?

    Thanks and looking forward your response.

    NB: I live in Sénégal, a west African country across the Atlantic Ocean. Our currency here is “Franc CFA” (XOF is the code used)”

    Reply

  • Hey Jon,
    I would be interested in creating VBA as shown in your videos. My concern would be that once I create the VBA code I would like to take this VBA improvements to my work computer and have it available for all of my excel work both at work and home. Since I am creating the VBA inside a workbook how can I store it on my computer and then take it to work with me (or visa versa). I would like to keep all the VBA macros in a folder on the excel toolbar to make them easy to access with the touch of a icon. If you can explain this concept it would be appreciated.

    Reply

    • Hey Joel,

      Great question! You could store the file on a cloud drive like Dropbox, Google Drive, or OneDrive. Then access it on any computer that is connected to that drive. I use all of these services and they each allow you to store files in folders on your computer. The folders look like regular folders and they sync whenever you make changes or add/delete files. It’s the easiest way to access files between computers and keep things synced.

      I hope that helps.

      Reply

  • Hi Jon,

    I missed the last course enrolment.

    Can you tell me when your next pro VBA course will start?

    In the Pro VBA course is there any help with plotting complicated equations from within VBA into a worksheet leaving the calculation in VBA rather the worksheet?

    Many thanks

    Steve

    Reply

  • This website is completely gret. I’ve researched these stuffs a great deal and I
    realised that is professional, easy to comprehend. I congratulate you because of this research that I’ll recommend to the people friends.
    I ask you to visit the gpa-calculator.co
    page where each scholar or scholar can calculate ratings grade point average
    marks. All good!

    Reply

  • This is ridiculously cool. Every time you think you have a pretty full understanding of Excel’s functions, something new and amazing shows up. Thanks very much for the video!!

    Reply

  • Hi John,

    Thank you for the easy to follow Intro to Excel Macros structure and tools. I have automated over time many Business Tasks with Lotus 123 and Excel Macros by using as you suggested the Macro Record to obtain the various steps in code format and then assembled the whole thing and polish it.

    Looking forward to what ‘s coming next.

    Thanking You

    Alain de Coriolis

    Reply

  • In this 3rd video, twice the ‘hint’ came up (once for .SaveAs and once for .Close) there was an enclosing set of parentheses around the parameters but you did not include them. I would have automatically typed “(” to begin the parameters. Why didn’t you need them? (I’m OCD 😉

    BTW, I have gotten quite a lot out of these three videos! Keep up the good work!

    Reply

  • Jon,

    Well done. Thanks.

    I would like to create a tool that enables (anyone who uses it) to create a Manifest file that would be used to upload a course to a Learning Management System (LMS). The file would be XML, thus would have to be able to use a combination of variables, formulas, loops, and special characters.

    Imagine a document that the Course Designer would have filled out items such as:
    * Course Title
    * Course Description
    * Course Chapters
    * Course Lessons Title
    * Course Lesson Filenames
    * Course Objectives

    Then once it’s all filled out, the automation creates the manifest file based upon the collected information above. Part of the file would look like:

    —— sample excerpt ——-

    [Lesson Title]

    —— sample excerpt ——-

    Then when it’s all finished, it saves the file as a text-based file with the .XML extension.

    NOTE:
    * No two courses would look alike.
    * Some courses may only have 1 chapter and (let’s say) 3 lessons. Whereas other courses may have 5 chapters and 25 lessons.

    I’ve got a lot to think about, but I’d be curious if you had any thoughts about this idea.

    Reply

  • HI Jon! Tks for the very well explained video. I am a Mac user so I wanna ask you a question.

    1. How can I open a file directly from the user form using your example.

    w/regards

    Reply

  • Hi Jon
    3 Awesome videos. I really enjoyed them. At first glance VBA seems quite complicated but you have shown with a bit of work you can start to understand VBA’s possibilities.

    One suggestion I would add is is there a way to format the dialog box. Everything looks very grey – how would you change the colour of the form. Small things but they all add to the feel of the work that is being shown to the user.

    I am enjoying all your videos. Hope to hear from you soon.

    Zia

    Reply

    • Hi Zia,

      Thank you for the nice feedback. Great suggestion! Yes, the userform background and controls can be formatted with different colors. We can make them look a bit more modern with some formatting techniques. I’ll add a video about this in the future.

      Thanks again and have a nice day! 🙂

      Reply

  • Hey! Thanks for these 3 videos, i’m not sure if this is the right place to ask this question, i’m certainly interested in the pro course, but there are some things that i don’t get and stop me from buying it… the thing is, where i’m from 300 bucks is a ton of money, actually 30% of my monthly income, and i know knowledge is always a good investment,… i should stop rambling
    How does the 30 day return policy work? i mean if the course is delivered entirely to the student as the payment finished its process (so that the student can own-pase the course), how can you be certain that no one can watch all videos in a week and ask for a return?? i assume the contents are kind of time locked? I’m not planning on asking for a return in any way, specially after watching plenty of videos from you in youtoube, i believe i can use all the info in the pro course and that’s enough for it to be worth, however from a business stand point i don’t get how you can offer the return policy as a guarantee… is it that you just trust that the student won’t do what i just said (watch all the content super fast and return it)? or do you have a way to defend against it like the one i said (time locked content)?
    This matter is primarily what is stopping me from spending 30% of my montly income u.u, i hope you can clarify it for me.-

    Thank you!

    PS: in the case i’m convinced to spend the money, do you accept paypal?

    Reply

  • Thank you so muj Jon,

    The 3 videos are really helpful to me as a beginner.

    Look forward to see more of your courses.

    have a great day!

    Reply

  • Hi, 1st thanks for nice video we learn too much from it
    so have a question please.

    need to copy this more than 2 or 3 times for same user from different file so how do it?
    Thanks

    Reply

  • I found the videos informative. I learned at a rapid pace. I had to stop the YouTube video to catch up often then to take pause and understand what was going on. In the end it was a quick way to go from zero to intermediate in only 3 nights or so.

    Reply

  • Great video. What would be useful is to see the reverse of this process. Importing individual sheets into a master sheet. Is this something that is covered in the Pro course?

    Reply

  • Hi Jon,

    I’ve watched all 3 of your videos and want to say THANKS.

    Just one small comment. I downloaded the xlsm file accompanying video 3. I tried running it on my D: drive after changing the filepath, but no new files were created.

    Anyways, after stepping through the code (F8) I ended up changing the following line from True to False:

    ‘check if items selected
    If Me.ListBox_Sheets.Selected(lSht) = True Then

    Now it works fine…

    Reply