Smart Data Collection with Excel UserForm

This lecture clearly demonstrate and quantify benefits of automating work with Office Documents with User Form. User Form helps to be more efficient, in particular, it helps to keep all information to one place and be concentrated on the task. A fun demonstration is performed using simple excel and same file but with addition of User Form. Results were showing that we are faster 2x with User Form to achieve the same amount of work

In addition to that, simple data collection done in Excel is very fast, does not require cloud infrastructure and can be very inexpensive.

On the other side, programming in VBA can be tricky, especially when dear Microsoft would cutout/ change some VBA features...

With this exercise you should be able to identify where it is better to apply the course know how while spending the right amount of time and achieving the maximum benefit

In this lecture we will learn how to import and reuse the UserForm and other provided code. This will also help you to know how to reuse your projects into the new activities

This lecture explains how the template work and how exactly we facilitate and streamline work with data to take decision and reporting.

This lecture explains how to open, close and manipulate fields in the UserForm. You will learn how to keep track of the Worksheet row and keep it as a variable in the VBA code. You will also learn how to close the User Form with a button and how to use basic properties of the User Form

This Lecture explains how do we use dedicated program to update fields that are inside User Form.

In this lecture you will learn how to find functions and actually see how they work. Functions are important in coding as they are allowing to make the code compact and understandable. Simple tip: name the function with Verb because they are doing something! 

Simple recommendation to keep observations in rows and categories in columns

In this lecture we are understanding how do we send data from UserForm fields into the Worksheet Report page.

In this case study you can practice and try to enhance given functionality by yourself. In particular you will master:

• Adding new fields and bringing information there
• Set User Form Fields properties programmatically and adding logic to data entry
• Reset values after the data sending

Comment: research in google is allowed (e.g. "excel vba how to reset values in userform" ...:)

Hopefully you have completed this Case Study yourself. Let's compare our solutions!

In this lecture I will uncover you the secret on why I started to work with User Form in the first place! Visual communication is very important however it's also important not to spend so much time with it!

This lecture just tackle the process of adding and modifying properties of Image Control of the User Form 

This lecture describes how to select picture from the computer or network location and to bring this picture to the User Form Image Control. You will also learn how to save image path to the variable because you will need to reuse it later

This lecture will finalize base line explanation on how to use images in the User Form. You will see how you can write picture to the destination worksheet with pixel-like precision. We will use a trick that help us to keep picture as a shape inside the cell.

We will continue fun scenario of IT expert helping others to save time and be more productive. As we are heading to the end of the course your tasks will be oriented on re-using the code and creating new functionalities of template. This way you will practice to be more prepared to apply this course for your personal benefit

In this lecture we will review how to solve the case study and also review which concepts we practiced. Download the file with solution using provided link so you can better follow the explanations of this lecture

Excel is made for calculations, Word for writing texts and reports, PowerPoint for presentations... By connecting documents you can save a lot of time and be super productive! Probably this concept deserve the special course by itself but in this lecture we will just review how to automate creation of the PowerPoint presentation from the data that we nicely keep in Excel table

In this Case Study you will improve our template even further. You will add more fields to the slide, customize them and apply PowerPoint template ... 

In this lecture we will review how to solve the case study and also review which concepts we practiced. Download the file with solution using provided link so you can better follow the explanations of this lecture

In this lecture I would share with you my personal recommendation on how to use provided template for your activities to improve working with Office documents. These will consist in 3 parts

1. Keep your code ready!
2. Apply code and expand template
3. Know the boundaries and limitations, explore the alternative solutions

This lecture explains what we have built in the course. The summary of all functionality achieved.

Just a final note

Sharing code for useful functions in Excel VBA.

Useful function to programmatically check presence of specific string

• on specific worksheet

• within column "A"

Function to programmatically find row location of specific string

• on specific worksheet

• within column "A"

Coming soon!

Build a user form to input data into a workbook so users are not directly inputting into the workbook to cut down on errors

use commands to print that data in a report,

filter data shown on the report by the date on entry

Another functionality. After completed work to collect the data it's good to have an option to easily 'push' the copy of the file to the 'corporate' SharePoint Document Library... all just by clicking a button!

Expanding our great Template by adding one more button... now we can also draft and send email from the UserForm

Very handy trick on collecting simple data within your work SharePoint /Office365 environment

Introduction to the Goal Setting and tracking Template.

Resources:

1. Ready to use file

2. Link to the latest version of this template

3. Link to the release 1.0 of this template on Github.com

Using template to set and update goals, Archiving records...

Using template to create PowerPoint summary presentation, creating and deleting Pivot Table to visually analyse the status

Brief overview of VBA code used to create this functionality