Macro Magic: Advanced Techniques and Tips

Learning Objectives

• To explain the keyboard shortcut that takes you to the last used cell within your workbook.
• To discuss which line of programming code would count the number of rows in a contiguous block of cells.
• To define the purpose of the word “With” in the context of an Excel macro.
• To analyze the method in Visual Basic for Applications that would display a UserForm.
• To explain the aspect of the Properties Window that allows you to change the wording that appears at the top of a Frame control.
• To identify the property that allows you to specify or unspecify one or more items within a ListBox.

Course Description

Unlock the potential of Excel macros in this comprehensive CPE webinar. Get ready to enhance your Excel skills and become proficient in writing programming code, making your Excel experience more efficient and interactive.

In this online course, David Ringstrom will guide you through creating macros that can reset workbooks with skewed scrollbars and instantly unhide all worksheets in a workbook. You'll delve into the world of programming code, learning to make crucial decisions by contrasting If and Select Case statements. Discover three powerful ways to create loops in Excel: For Each, Do While, and Do Until. Also, I gained valuable insights into troubleshooting problematic loops and avoiding Excel crashes.

But that's not all. David will take you even further by demonstrating how to create custom dialog boxes for added interactivity. Learn to manage user actions effectively and build a custom printing interface called "Plug and Print." This interface lets you handpick worksheets to print from any workbook, making your Excel tasks more user-friendly. You'll also master creating UserForms, enabling users to input data seamlessly. David will show you how to move module sheets and UserForms between workbooks and remove them when they're no longer needed.

Key Topics Covered in this CPE Excel Course:

• Displaying the Project Explorer and Properties windows within Excel’s Visual Basic Editor.
• Providing feedback to a user by way of the Msg Box method within a macro.
• Contrasting Do While and Do Until Loops.
• Exploring three ways to cause a macro that’s running in Excel to halt.
• Streamlining programming code by using the With…End With a statement.
• Returning the underlying number for a color assigned to a worksheet cell for use elsewhere.
• Making decisions in Excel by way of IF statements.
• Learning how to use a single line of programming code to unhide all worksheets within a workbook.
• Inserting blank module sheets into Excel workbooks.
• Consolidating data from multiple workbooks into a single worksheet by way of an Excel macro.
• Incorporating a new feature that enables you to pick and choose the worksheets to print from any workbook—no programming knowledge or experience required.
• Placing ListBoxes on UserForms empowers you to make a single choice or multiple choices from a list.
• Creating a macro to launch a UserForm.
• Dressing up UserForms with Frames as well as using Frames to isolate sets of Option Buttons.
• Contrasting Option Buttons to Checkboxes on UserForms for allowing a single selection from two or more choices.
• Adding label controls to UserForms to add captions or display instructions.
• Building a simple UserForm for data entry purposes in Excel.
• Triggering macros in Excel by way of Command Buttons added to UserForms.
• Creating a UserForm, or custom dialog box, from scratch in Excel.

Don't miss this opportunity to become an Excel macro pro and elevate your productivity. Whether you're a beginner or an experienced Excel user, this webinar will empower you with the knowledge and skills to create efficient and interactive Excel macros. Register now and take your Excel game to the next level!

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly known as Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Overview

• Replicating the big sheet problem

  5 mins
• Automating the last cell reset

  25 mins
• VBE project explorer and properties windows

  51 mins
• Determining if the workbook matches criteria

  79 mins
• Do until loop

  106 mins
• UserForm ListBoxs

  137 mins
• Creating a UserForm_Initialize Event

  166 mins
• Finishing UserForm launch macro

  192 mins
• Create a data entry form that puts detail into worksheet

  214 mins