Excel VBA: ActiveX Controls Part 2. Excel VBA 2 Lesson 4

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

Topics Common to ActiveX controls

All controls have a default property and a default event.  Controls in bold are covered in this lesson.

Once a control is created, the default event handler can be accessed simply by double-clicking it.  The Private Module will be brought up and the default event handler created.

All controls can be included in the sequence of controls selected in turn by the Tab key.  If TabStop is True, then the control is included.  The TabIndex specifies the sequence of the Tab enabled controls, beginning with 0.

All controls have location, sizing and display properties such as Left, Top, Height, Width, BorderColor, BorderStyle, and BackColor (background fill color).  All controls can have ControlTipText set, which is displayed during Run Time when the mouse is hovered over the control.  All controls can have Disabled set to False, where the user can see the control, but can’t click it., and can have Visible set to False, where the user can’t even see it.

Controls with a value can keep the information within the control, or link to a cell via the ControlSource property.  If ControlSource is set, then editing the displayed Value changes the cell’s contents and changing the cell contents changes the displayed Value.

Information kept only within a control is transient, and is lost when the UserForm is unloaded, while information linked to cells is retained until the next use of the UserForm, and if the file is saved after the usage, is retained until the next use of the file.  Transient data can also be lost in the case of an error, which may complicate error recovery.  Transient data will be retained if the UserForm is hidden with the Hide method, while it is lost if the Unload method is used.

If there will be multiple instances of a control, create the first one and copy it to repeat all the properties, such as size, caption, and 3D effects.  It is therefore most efficient to design the first one as completely as possible before copying.  When a control is copied, then pasted, the pasted instance will be placed centrally on the UserForm.  It is better to copy by dragging, so the placement of the new instance is handled at the same time.  To do this, press <Ctrl> while left-clicking and dragging.  Pressing both <Ctrl> and <Shift> while left-clicking and dragging will result in a placement that is aligned either horizontally or vertically with the first control.

Except for Frame, TabStrip, and Multipage, additional information is available from Microsoft Excel Help, as links from the help page for ActiveX Controls.  Additional help for the exceptions is found under TabStrip Control, MultiPage Control, and Frame Control.

Labels

Labels are the simplest control, only used for giving information to the user.  They are typically set up at design time.  The default property for a Label is the Caption property, and the default event is the Click event.  The caption can be changed by code but not by the user.  Label events are seldom used, since users are accustomed to read them but not interacting with them.

EXAMPLE

From the Lesson 2 sheet of the demo file, click the “Start Label and TextBox Demos” button for Label, TextBox, and CommandButton examples.  The caption texts explain themselves.

TextBoxes

TextBoxes are used to receive input from the user, or to allow the user to edit data.  Though it is called a TextBox, it is not limited to text.  While the default is to return text, it can just as easily return numeric values.  The validation of user input will be covered at length in Lesson 9.

Using VBE (Visual Basic Editor)

VBE Toolbars

EXERCISE

If you know already how to use the standard VBE toolbars, this exercise is optional.

Toolbars

View>Toolbars will open a menu that lists four toolbars:  Debug, Edit, Standard, and UserForm.  The first three should be checked.  Whether they are docked and where they are located is largely a matter of preference.  A frequently used configuration is for Standard to be docked at the top, just below the main VBE tool bar, and the Debug and Edit toolbars to be docked at the bottom, with Debug on the left.

There are many handy icons on each toolbar, but some frequently used ones are:

Standard: Save, Run Sub/UserForm, Reset

Debug: Run Macro, Reset, Toggle Breakpoint, Step Into, Step Over, Step Out

Edit: Toggle Breakpoint, Comment Block, Uncomment Block, Toggle Bookmark, Next Bookmark, Previous Bookmark

Locate each of these on their respective toolbars by pausing the cursor over each icon in turn and checking the control tip.  There are other ways to do most of these functions, including short cut keys and drop-down menu items.  Which method used is also a matter of preference.

For general knowledge, investigate all the icons.

VBE Windows and Panes

EXERCISE

If you know already how about VBE windows and panes, this exercise is optional.

Navigating between Object View and Code View

The left pane of the VBE window is VBAProject and the right pane is either the Code Pane or the Object Pane.  Right-click “UserForm1” in VBAProject and note the top two entries are View Code and View Object. These are for navigating between the two views.  Double-clicking “UserForm1” also will return from Code View to Object View.  Depending on the settings, there may also be a bar at the top of VBAProject which has icons for navigating to Code View and Object View.

View>Immediate Window.  This may also be docked or not.  A common docking spot is at the bottom of the right pane, below the Code View or Object View.  Debug.Print is the line of code that writes to the Immediate Window.

If F2 is accidentally pressed, the Object Browser will be displayed in the right pane.  It will probably be maximized in the right pane, and is best closed by clicking the small “x” just below the large “X” in the upper right corner of the VBE window.

TextBoxes (continued)

EXERCISE

Create a textbox named TextBox1

Double-click it.  The default Change event handler will be created in the View Code Pane.

Private Sub TextBox1_Change()

End Sub

Enter the two lines of code shown below, and then enter the DataValidationMacro and UserForm_Initialize procedures.  It may be typed, or copied to the clipboard and pasted.

Private Sub TextBox1_Change()

    Debug.Print TextBox1.Value         ’ writes to the Immediate Window

    Call DataValidationMacro

End Sub

Private Sub DataValidationMacro ()

    Debug.Print “DataValidationMacro”  ’ writes to the Immediate Window

End Sub

Private Sub UserForm_Initialize()

End Sub

Place the cursor in UserForm_Initialize and click one of the Run icons.  The UserForm will open.  

HOME | Back to index  <Previous | Next> Buy the complete Excel course, Including VBA & Workbook examples for only $9.95 | Ask a question?

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.