Dive into advanced Visual Basic for Applications (VBA) techniques in Excel 2019 with this comprehensive tutorial. Master the creation of custom forms and controls, learn to manipulate pivot tables programmatically, and automate complex processes. Explore UserForm objects, various control types, and their properties while building interactive interfaces. Develop skills in coding form controls, working with text files, and implementing advanced pivot table operations through VBA. Gain hands-on experience in creating sub-procedures, modifying code for form objects, and enhancing Excel's functionality with custom tools. By the end of this 1 hour and 42 minutes tutorial, acquire the expertise to design sophisticated Excel solutions that streamline data analysis and reporting tasks.
Overview
Syllabus
Start
Introduction
Introduction to Forms and Controls
UserForm Object-Controls-Properties-Events-Methods
Reviewing Types of Modules and Inserting UserForm Objects
Changing Form Properties
Creating a Label Control and Adjusting its Properties
Copying a Label Control to Create a New One
Adding Two Text Box Controls to Forms
Adding Two Command Button Controls to Forms
Adding a Combo Box Control and Label
Adding Two Frame Controls with Three Option Buttons in Each
Adjusting Option Button Properties
Aligning and Grouping Form Controls
Testing and Adjusting Form Tab Order
Coding the Combo Box to Display Months
Coding Other Form Controls Using Text Files
Reviewing Coding in Text Files
Displaying the Form Programmatically
Forms and Controls Recap
Introduction to Pivot Tables in VBA
Introduction to Creating Pivot Tables
Creating Pivot Tables by Recording a Macro
Creating Pivot Tables
Testing CreatePivot Sub-Procedure
Creating Sub-Procedures to Set Pivot Table Fields
Finalizing Forms
Creating Sub-Procedures to Code Options in the Period Frame on Forms
Reviewing GetLastDate and GetFirstDate Code Blocks from Text Files
Coding Options in the Sales Frame on the Form
Reviewing Text File Procedures - ConsolidateData-GrabCells-FinishReport
Completing Code Modifications for Form Objects
Testing Completed Forms
Adding Icons to the QAT to Run the Form
Pivot Tables Recap
Conclusion
Taught by
Learnit Training
