13 Excel Tables, Formulas & Pivot Tables
Overview: (Pivot) Tables & Formulas
Using Excel Formulas in VBA
Excel Tables: Use the Right Syntax
Pivot Tables & VBA: What you Need to Know
Activity: Update Existing Formulas with VBA by Adding IFERROR
Project: Update Existing Formulas with VBA by Adding IFERROR
Key Takeaways (Pivot) Tables & Formula
14 Main Project #2: Regional Reporting Tool
What You'll Learn (& Should be Aware of..)
Project Overview: Regional Sales Reporting Tool
Importing Data from Multiple Files using MultiSelect
Setting up a Template as the Basis for Different Reports
Exporting Reports as Excel files (Pivot Table & Hard Coded Reports)
Fine-tuning Report Export Procedure (Double-check if user wants to overwrite)
Setting up Automatic Export of Text File (Fix delimiter in CSV to Semicolon)
Track the List of Tasks Completed with VBA
Final Touches & Creating Interface to Hide Specific Tabs with a Password
Key Takeaways & Tips for VBA based Excel Tools
Second Milestone Completed!
15 Interacting with Other Applications (Word, PowerPoint etc.)
Overview & Important Concepts when Interacting with Other Applications
Project PDF: Save Specific Sheets as a PDF File
Project Email: Automatically Create Personalized Emails with Attachments
Project Microsoft Word: Create Personalized Letters in Word from Excel
Project Microsoft PowerPoint: Export Excel Sheets to PowerPoint Presentation
Key Takeaways when Working with other Applications
16 Worksheet and Workbook Events
Overview & What are Event Procedures?
Workbook Events, Toggle Full Screen (Activate, Deactivate, Open, Close)
Worksheet Selection Change: Dynamic Font Color of Selected Range
Refresh PivotTables Automatically & Concept of Range Intersection
Resetting a Dependent Drop-down when the first Drop-down is Changed
Activity: Link Page Header to Cell Value (BeforePrint Event)
Project: Link Page Header to Cell Value (BeforePrint Event)
2 Useful Tips when Working with Events & Key Takeaways
17 Working with Userforms & ActiveX Controls
Overview: UserForms & ActiveX Controls
ActiveX Controls: What you Need to Know
ActiveX Check Box to Fix Scroll Area & Toggle Screen Settings
ActiveX to Show and Hide Help
ActiveX Combo Box for Easy Sheet Navigation (Great for Larger Workbooks)
ActiveX to Show or Hide Other ActiveX controls (eg. Actual & Budget reporting)
UserForm Basics: What You Need to Get Started
UserForm for Data Entry (Input & Check Boxes)
UserForm to Document External Links & Comments (Combo Box & Option button)
Bonus: ListBox to Print Multiple Pages in ONE Go
UserForm Checklist & Course Map Status
18 Main Project #3: Invoice Generation Tool
Tool Overview: What You Will Learn
Setting up an Input UserForm to Collect Master Data
Working with Multi-Column ListBoxes
Edit Master Data with UserForm
Searchable ListBox (with Selection Change event)
Enhanced Searchable ListBox: Write Selection back to Excel Table
MultiPage UserForm to Create Invoices & Email (Setup)
Export PDF & Excel Workbook for Each Invoice based on ListBox Selection
Create Email in Outlook & Attach PDF Invoice
Final Updates & Debugging
Last Milestone Completed!
19 Creating Customized Functions / Formulas
Section Overview: Function Procedures
Setting up a Simple Function (including Optional Arguments)
Function to Calculate Age (Result in Years & Months)
Functions that Split Text and Numbers from Cell Value
Using Functions in Sub Procedures
Activity: Function That Sums based on Cell Color
Project: Function That Sums based on Cell Color
Key Takeaways Function Procedures in VBA & Course Map Status
20 Working with Charts
The Chart Object Library & Section Overview
Create and Modify a Chart
Animated Charts: Different Methods
Show Charts inside UserForms
Key Takeaways: Charts
21 Summary & Final Words
Your Next Steps & more Learning
BONUS Lecture: More from Leila
