Excel Training VBA Lesson 25

SpinButton, ScrollBar, TextBox and ComboBox

 Information Helpful? Why Not Donate >> Excel Training-Video Series

<< PREVIOUS LESSON | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

Workbook Download
    This is a zipped Excel Workbook to go with this lesson.

SpinButton and ScrollBar

LargeChange (ScrollBar only)

This property sets the amount the user can move when the user clicks between the Scroll box and Scroll arrow.

Max and Min

This property determines the Maximum or Minimum Value the SpinButton or ScrollBar will increment to. The setting must be a Integer.

Orientation

This will make the Control either vertical or horizontal. The default is for fmOrientationAuto which means Excel will position the Control based on the Controls dimensions.

SmallChange

Determines how movement will occur whenever a user spins or scrolls up or down. The default setting is 1. The Value must be an Integer.  

Let's now look at the TextBox and some of it's Properties that have not been mentioned.

TextBox and ComboBox

AutoWordSelect

This Property takes a Boolean and determines what will be the base unit that is used to extend a selection. When set to TRUE the base unit is a word. When set to FALSE the base unit is a single character. If AutoWordSelect is set to TRUE and the user places the mouse insertion point into the TextBox in the middle of a word and then extends (drags) the selection, the entire word is selected. Doing this when AutoWordSelect is set to FALSE would mean only one character at a time would be selected.

DragBehaviour

This Property can be either enabled (fmDragBehaviorEnabled) or disabled (fmDragBehaviorDisabled). When enabled the user can drag-and-drop (cut or copy and paste). If the Property is disabled and the user drags within the TextBox any text is only highlighted.

EnterFieldBehaviour

This Property sets the method in which the text is selected when entering a TextBox. Its two settings are fmEnterFieldBehaviorSelectAll  (default) and fmEnterFieldBehaviorRecallSelection. when left set as fmEnterFieldBehaviorSelectAll the entire content of the TextBox is selected when the user enters the TextBox. If set to

fmEnterFieldBehaviorRecallSelection the selection is the same as the last time the Control was active. 

This only applies when the user Tabs to the Control.

HideSelection

Takes a Boolean and determines whether selected Text still appears selected when the Control no longer has Focus. Setting this to TRUE (default) means Text is not highlighted unless the Control has Focus.

MaxLength

This Property sets the maximum number of characters that can be placed into a Control. The default is 0 (zero) which means the Control has no limit set and any number of characters can be entered.

MultiLine (TextBox only)

Specifies whether a Textbox can have multiple lines of Text. The setting is a Boolean with the default being TRUE. If set to FALSE the Textbox will only ever have one line of Text regardless of the number of characters and size of the Textbox.

PasswordChar (TextBox only)

This Property determines whether *placeholder characters are displayed when the user types in a TextBox. The setting can be any String.

*placeholder

A character that masks or hides another character for security reasons. For example, when a user types a password, an asterisk is displayed on the screen to take the place of each character typed.

So as you can see we are able to set the visual and actual effect that happens when the user does anything to a Control. The Properties can be used in different combinations to produce different effects. Most Properties can be set either at Design-time or Run-time. While some Properties can only be set at Design-time. The Font type is one of these. While we can set the Fonts attributes Bold, Italic, Underlined etc we cannot change the Font type, eg; Ariel to Times New Roman at Run-Time.

Controls Parent

As mentioned earlier whenever a Control is placed on a Worksheet is becomes a Object of the Worsheet. This means it is part of the Worksheets Object collection. This basically means that if we want to access a Control that is on a Worksheet we must first go through the Worksheet Object as the Controls Parent is the Worksheet that has the Control embedded in it. Lets say we have a TextBox on a Worksheet who's CodeName is Sheet1 and we want to Select it. We would use:

Sub SelectKnownTextBox()
    Sheet1.TextBox1.Select
End Sub

Very easy indeed! But lets assume we have no idea of the name of the TextBox all we know is that a TextBox does exists on Sheet1. In this case we will need to Loop through all Objects on the Worksheet until we find one that is a TextBox. We can determine this by using the ProgId Property. But before we go and Loop through ALL Objects we can narrow our search down to the type of Object we are interested in. For ActiveX Controls the type is an OLEObject and the OLEObject is a member of the OLEObjects Collection. So we could use this method:   

Sub SelectUnknownTextBox()
Dim tBox As OLEObject

So as you can see we have used a For Each (with each referring to OLEObjects) to Loop through only OLEObjects that are on Sheet1. We then use the ProgId Property to check and see if the OLEObject is a TextBox or not. The list of identifiers for ActiveX Controls is shown below. The identifier of a ActiveX Control can also be seen in the Formula bar when the Control is Selected, eg; =EMBED("Forms.TextBox.1","")

  

To create this control	Use this identifier
CheckBox	Forms.CheckBox.1
ComboBox	Forms.ComboBox.1
CommandButton	Forms.CommandButton.1
Frame	Forms.Frame.1
Image	Forms.Image.1
Label	Forms.Label.1
ListBox	Forms.ListBox.1
MultiPage	Forms.MultiPage.1
OptionButton	Forms.OptionButton.1
ScrollBar	Forms.ScrollBar.1
SpinButton	Forms.SpinButton.1
TabStrip	Forms.TabStrip.1
TextBox	Forms.TextBox.1
ToggleButton	Forms.ToggleButton.1

 

So using the above list we can access any of the above mentioned ActiveX controls on any Worksheet.

Events

As we have Events for the Workbook Object and Worksheet Object we also have Events for ActiveX Controls. We can see all the Events associated with a particular Control by viewing it's code and looking in the Procedure box. We can gain access to the code for a Control in two ways. Right click on the Control and select "View Code" or Double click the Control. For both methods we must be in Edit Mode.Whichever method we use, Excel will open up the Private Sub of the Worksheet. The Procedure written by default will be the default Procedure for the Control. This is usually the Change Event. For example double clicking a TextBox gives us:

Private Sub TextBox1_Change()

We can then see all the Events this Control has by placing our mouse insertion point anywhere within the Procedure and clicking the drop down arrow in the Procedure box (top right of the Module). Most Controls have around 15 Events that are available to them. As with the Events for a Workbook or Worksheet any code within an Event Procedure for a Control will run whenever the Event occurs. The way in which we apply these Events is purely up to us. 

For the rest of the lesson I have attached a Workbook that has Controls on it and some code placed within the Event Procedures. Browse through these at your leisure as the code is only very simple. We will look at the Events for Controls in the Excel UserForms course.

<< PREVIOUS LESSON | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX