Working with controls and automation focuses on using Form controls and ActiveX controls on worksheets, controls on UserForms, and using automation to control Microsoft Word, Microsoft Outlook, and Microsoft Internet Explorer from Excel.
Adding controls to a worksheet and a UserForm, creating a Word document using values stored in a worksheet, sending emails with a file attached via Outlook to list of recipients stored in a worksheet, entering some text in a text box and clicking a command button on a web page via Internet Explorer are some of examples of automation.
To understand better a concept discussed in this book, simply copy and paste the sample VBA code to the Immediate window, a standard VBA module, or a worksheet module, and run the VBA code or step through the VBA code by using the debugger in VBE. To execute a few lines of code that are not in a Sub procedure (also known as macro), simply enclose them in a new procedure. When the book touches on UserForm, much of VBA code is in UserForm modules.
Table of Contents
Chapter 1: Working with controls
Form controls versus ActiveX controls on a worksheet
Determine whether a control on a worksheet is a Form control or an ActiveX control
A non-VBA way of adding controls
A Form control and an ActiveX control on a worksheet
Some controls on a UserForm
Form Controls on a worksheet
Refer to a Form control
Refer to Form controls of a type
Add a Form control to a worksheet
Determine the type of a Form control on a worksheet
Assign a macro to a Form control and disconnect the macro from the control
Disable and hide a Form control
The way a Form control attached to a cell
Move a Form control
Copy a Form control
Loop through all Form controls
Loop through Form controls of a particular type
Some operations on …
Button
Option button and group box
Label
Check box
List box
Combo box (Drop down)
Spinner
Scroll bar
ActiveX controls on a worksheet
Refer to an ActiveX control
Add an ActiveX control to a worksheet
Determine the type of an ActiveX control on a worksheet
Identify events for an ActiveX control
Disable and hide an ActiveX control
Change the caption and font of an ActiveX control
The way an ActiveX control attached to a cell
Move an ActiveX control
Copy an ActiveX control
Loop through ActiveX controls
Loop through ActiveX controls of a particular type
Some operations on …
Command Button
Option button
Frame control
Label
Check box
Text box
Spin button
Scroll bar
Image control
Controls on a UserForm
Basic steps in creating a working UserForm
Set the tab order of controls
Consider hot keys for controls
Refer to controls on a UserForm
Disable and hide a control on a UserForm
Change the caption and the font of a control on a UserForm
Determine the type of a control on a UserForm
Move a control within a UserForm
Center a control on a UserForm
Center UserForm on the Excel window
Dynamically add a control that is responsive to events
Loop through all controls on a UserForm
Loop through controls of a particular type
Loop through controls of a type on all Pages of a MultiPage control
Loop through controls of a type on a page of a MultiPage control
Display a timer on a UserForm
Display a countdown timer on a UserForm
Drag around a control on a UserForm
Simulate a free-falling object on a UserForm
Link a range of cells to a list box
Chapter 2: Automation
Early binding versus late binding
Working with the Word application
Create a document behind the scene
Paste data from an Excel workbook to specific locations on a Word document
Working with Outlook application
Send a simple email
Send an email with a workbook or file attached
Send an email with a sheet attached
Send an email to each person with a specific file
Working with Internet Explorer
Navigate to a web page
Click a hyperlink on a web page
Ways to identify HTML elements
Check an option button on a web page
Click a button control on a web page
Select an item from a combo box
Extract URLs of hyperlinks
Sign in to an account
Find the right Internet Explorer window among all open windows
Dissecting Excel VBA 