Basics of VBA / Macros Chapter – 78 (ActiveX Controls Part-7)

Spin Button

A spin button can be used to increment a number in a cell. To create a spin button in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Spin Button.

Create a spin button in Excel VBA

3. Drag a spin button on your worksheet.

4. Right click the spin button (make sure Design Mode is selected).

5. Click View Code.

View Code

You can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave SpinButton1 as the name of the spin button.

6. To link this spin button to a cell, add the following code line.

Range(“C3”).Value = SpinButton1.Value

7. You can set a maximum and minimum by adding the following code lines.

SpinButton1.Max = 100
SpinButton1.Min = 0

8. To change the incremental value, use the SmallChange property.

SpinButton1.SmallChange = 2

9. Click the arrows of the spin button (make sure Design Mode is deselected).

Result:

Spin Button

Instead of changing the properties of the spin button at runtime, you can also change the properties at design-time. To achieve this, right click on the spin button (make sure Design Mode is selected) and click on properties.

Properties

Basics of VBA / Macros Chapter – 77 (ActiveX Controls Part-6)

Option Buttons

If you have more than one option button, only one of the option buttons can be selected. To create option buttonsin Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Option Button.

Create an option button in Excel VBA

3. Drag two option buttons on your worksheet.

4. Right click the first option button (make sure Design Mode is selected).

5. Click View Code.

View Code

You can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the captions of the option buttons to Female and Male. For now, we will leave OptionButton1 and OptionButton2 as the names of the option buttons.

6. Add the following code line:

If OptionButton1.Value = True Then Range(“D3”).Value = 10

7. Right click the second option button (make sure Design Mode is selected).

8. Click View Code.

9. Add the following code line:

If OptionButton2.Value = True Then Range(“D3”).Value = 20

10. Click the option buttons on the sheet (make sure Design Mode is deselected).

Result:

Option Button 1 True

Option Button 2 True

Although in some situations it can be useful to directly place option buttons on your worksheet, option buttons are particularly useful when placed on a Userform.

Basics of VBA / Macros Chapter – 76 (ActiveX Controls Part-5)

Check Box

A check box is a field which can be checked to store information. To create a check box in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Check Box.

Create a check box in Excel VBA

3. Drag a check box on your worksheet.

4. Right click the check box (make sure Design Mode is selected).

5. Click View Code.

View Code

Note: you can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the check box to MyCheckBox. For now, we will leave CheckBox1 as the name of the check box.

6. Add the following code lines:

If CheckBox1.Value = True Then Range(“D2”).Value = 1
If CheckBox1.Value = False Then Range(“D2”).Value = 0

7. Click the check box on the sheet (make sure Design Mode is deselected).

Result:

Check Box True

Check Box False

Although in some situations it can be useful to directly place a check box on your worksheet, a check box is particularly useful when placed on a Userform.

Basics of VBA / Macros Chapter – 75 (ActiveX Controls Part-4)

Combo Box

A combo box is a drop-down list from where a user can select an item or fill in his/her own choice. To create a combo box in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Combo Box.

Create a combo box in Excel VBA

3. Drag a combo box on your worksheet.

Drag a Combo Box

You can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ComboBox1 as the name of the combo box.

Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

4. Open the Visual Basic Editor.

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

Workbook Open Event in Excel VBA

7. To add items to the combo box, add the following code lines to the Workbook Open Event:

With Sheet1.ComboBox1
.AddItem “Paris”
.AddItem “New York”
.AddItem “London”
End With

Note: use Sheet2 if your combo box is located on the second worksheet, Sheet3 if your combo box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open event, you might want to add the code lines below before these code lines. The first code line clears the combo box. This way your items won’t be added multiple times if you execute your code more than once. The second code line clears your own choice.

ComboBox1.Clear
ComboBox1.Value = “”

8. To link this combo box to a cell, right click on the combo box (make sure design mode is selected) and click on Properties. Fill in D2 for LinkedCell.

LinkedCell

Also see the ListFillRange property to fill a combo box with a range of cells.

9. Save, close and reopen the Excel file.

Result:

Combo Box

Although in some situations it can be useful to directly place a combo box on your worksheet, a combo box is particularly useful when placed on a Userform.

Basics of VBA / Macros Chapter – 74 (ActiveX Controls Part-3)

List Box

A list box is a list from where a user can select an item. To create a list box in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click List Box.

Create a list box in Excel VBA

3. Drag a list box on your worksheet.

Drag a List Box

Note: you can change the name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. For now, we will leave ListBox1 as the name of the list box. Create a Workbook Open Event. Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

4. Open the Visual Basic Editor.

5. Double click on This Workbook in the Project Explorer.

6. Choose Workbook from the left drop-down list and choose Open from the right drop-down list.

Workbook Open Event in Excel VBA

7. To add items to the list box, add the following code lines to the Workbook Open Event:

With Sheet1.ListBox1
.AddItem “Paris”
.AddItem “New York”
.AddItem “London”
End With

Use Sheet2 if your list box is located on the second worksheet, Sheet3 if your list box is located on the third worksheet, etc. If you use these code lines outside the Workbook Open event, you might want to add the following code line before these code lines. This code line clears the list box. This way your items won’t be added multiple times if you execute your code more than once.

ListBox1.Clear

8. To link this list box to a cell, right click on the list box (make sure design mode is selected) and click on Properties. Fill in D3 for LinkedCell.

LinkedCell

Also see the ListFillRange property to fill a list box with a range of cells.

9. Save, close and reopen the Excel file.

Result:

List Box

Although in some situations it can be useful to directly place a list box on your worksheet, a list box is particularly useful when placed on a Userform.

Basics of VBA / Macros Chapter – 72 (ActiveX Controls Part-1)

ActiveX Controls

Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc. To create an ActiveX control in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. For example, in the ActiveX Controls group, click Command Button to insert a command button control.

Create an ActiveX control in Excel VBA

3. Drag a command button on your worksheet.

4. Right click the command button (make sure Design Mode is selected).

5. Click View Code.

View Code

You can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the command button to ‘Apply Blue Text Color’. For now, we will leave CommandButton1 as the name of the command button.

The Visual Basic Editor appears.

6. Add the code line shown below between Private Sub CommandButton1_Click() and End Sub.

Add Code Lines

7. Select the range B2:B4 and click the command button (make sure Design Mode is deselected).

Result:

Run Code

Basics of VBA / Macros Chapter – 73 (ActiveX Controls Part-2)

Text Box

A text box is an empty field where a user can fill in a piece of text. To create a text box in Excel VBA, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Text Box.

Create a text box in Excel VBA

3. Drag a command button and a text box on your worksheet.

4. Right click the command button (make sure Design Mode is selected).

5. Click View Code.

View Code

Note: you can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the command button to Import Data. For now, we will leave TextBox1 as the name of the text box.

6. Add the following code line:

TextBox1.Text = “Data imported successfully”

7. Click the command button on the sheet (make sure Design Mode is deselected).

Result:

Text Box

8. To clear a text box, use the following code line:

TextBox1.Value = “”

Although in some situations it can be useful to directly place a text box on your worksheet, a text box is particularly useful when placed on a Userform.

Basics of VBA / Macros Chapter – 71 (Application Object Part-4)

Write Data to Text File

Below we will look at a program in Excel VBA that writes an Excel range to a CSV (comma-separated-value) text file.

Write Data to Text File in Excel VBA

Place a command button on your worksheet and add the following code lines:

1. First, we declare a variable called myFile of type String, an object called rng of type Range, a variable called cellValue of type Variant, a variable called i of type Integer, and a variable called j of type Integer. We use a Variant variable here because a Variant variable can hold any type of value.

Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j AsInteger

2. We need to specify the full path and the filename of the file.

myFile = Application.DefaultFilePath & “\sales.csv”

Note: the DefaultFilePath property returns the default file path. The path to the folder you see when you open or save a file.

3. We initialize the range object rng with the selected range.

Set rng = Selection

4. Add the following code line:

Open myFile For Output As #1

Note: this statement allows the file to be written to. We can refer to the file as #1 during the rest of our code. If the file already exists, it will be deleted and a new file with the same name will be created.

5. Start a Double Loop.

For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count

rng.Rows.Count returns the number of rows (17 in this example) and rng.Columns.Count returns the number of columns (4 in this example).

6. Excel VBA writes the value of a cell to the variable cellValue.

cellValue = rng.Cells(i, j).Value

7. Add the following code lines to write the value of cellValue to the text file.

If j = rng.Columns.Count Then
Write #1, cellValue
Else
Write #1, cellValue,
End If

Due to the If Then Else statement, Excel VBA only starts a new line (Write #1, cellValue) when j equals the number of columns (last value in a row). To separate the values with a comma, use Write #1, cellValue, (with a comma).

8. Don’t forget to close both loops.

    Next j
Next i

9. Close the file.

Close #1

10. Select the data and click the command button on the sheet.

Result:

Write Data to Text File Result

Basics of VBA / Macros Chapter – 70 (Application Object Part-3)

Read Data from Text File

Below we will look at a program in Excel VBA that reads data from a text file. This file contains some geographical coordinates we want to import into Excel.

Read Data from Text File using Excel VBA

1. First, download the text file and add it to “C:\test\”

Place a command button on your worksheet and add the following code lines:

2. We declare four variables. myFile of type String, text of type String, textline of type String, posLat of type Integer, and posLong of type Integer.

Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer

3. We need to initialize the variable myFile with the full path and the filename.

myFile = “C:\test\geographical-coordinates.txt”

or use the GetOpenFilename method of the Application object to display the standard open Dialog box and select the file (without actually opening the file).

myFile = Application.GetOpenFilename()

The empty part between the brackets means we give Excel VBA nothing as input. Place your cursor on GetOpenFilename in the Visual Basic Editor and click F1 for help on the arguments.

4. Add the following code line:

Open myFile For Input As #1

Note: this statement allows the file to be read. We can refer to the file as #1 during the rest of our code.

5. Add the following code lines:

Do Until EOF(1)
    Line Input #1, textline
text = text & textline
Loop

Until the end of the file (EOF), Excel VBA reads a single line from the file and assigns it to textline. We use the & operator to concatenate (join) all the single lines and store it in the variable text.

6. Close the file.

Close #1

7. Next, we search for the position of the words latitude and longitude in the variable text. We use the Instrfunction.

posLat = InStr(text, “latitude”)
posLong = InStr(text, “longitude”)

8. We use these positions and the Mid function to extract the coordinates from the variable text and write the coordinates to cell A1 and cell A2.

Range(“A1”).Value = Mid(text, posLat + 10, 5)
Range(“A2”).Value = Mid(text, posLong + 11, 5)

9. Test the program.

Result:

Read Data from Text File Result

Basics of VBA / Macros Chapter – 69 (Application Object Part-2)

StatusBar

The StatusBar property of the Application object in Excel VBA can be used to indicate the progress of a lengthy macro. This way, you can let the user know that a macro is still running. The macro we are going to create fills Range(“A1:E20”) with random numbers.

Excel VBA StatusBar Property Example

Add the following code lines to the command button:

1. First, we declare three variables of type Integer, named i, j and pctCompl.

Dim i As Integer, j As Integer, pctCompl As Integer

2. Add a Double Loop.

For i = 1 To 20
For j = 1 To 5
Next j
Next i

Add the following code lines (at 3, 4 and 5) to the loop.

3. Use the RandBetween function to import a random number between 20 and 100.

Cells(i, j).Value = WorksheetFunction.RandBetween(20, 100)

4. Initialize the variable pctCompl. The second code line writes the value of the variable pctCompl and some descriptive text in the status bar.

pctCompl = (i – 1) * 5 + (j * 1)
Application.StatusBar = “Importing Data.. ” & pctCompl & “% Completed”

Example: For i = 3, j = 1, (3 – 1) * 5 + (1 * 1) = 11% has been completed.

5. We use the Wait method of the Application object to simulate a lengthy macro.

Application.Wait Now + TimeValue(“00:00:01”)

6. To restore the default status bar text, set the StatusBar property to False (outside the loop).

Application.StatusBar = False

Result when you click the command button on the sheet:

Excel VBA StatusBar Property Result

You can interrupt a macro at any time by pressing Esc or Ctrl + Break.