Excel VBA Controls Collection - Easy Excel Macros

SAVE OFFLINE

Excel  Easy #1 Excel tutorial on the net

Excel

Introduction

Controls Collection

Basics

Functions

Data Analysis

  When creating Userforms in Excel VBA, you can use the Controls collection to easily loop through controls and set a property of each control to a specific value. The Userform we are going to create looks as follows:

VBA

300 Examples

Search Entire Site

Ask us

Go

Chapter    Userform

Learn more, it's easy Userform and Ranges Currency Converter Progress Indicator Multiple List Box Selections Multicolumn Combo Box Dependent Combo Boxes Loop through Controls Controls Collection Userform with Multiple Pages Interactive Userform

Download Excel File controls-collection.xlsm

Follow Excel Easy

Become an Excel Pro 300 Examples  

To create this Userform, execute the following steps: 1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer. 2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the label, text boxes (first at the top, the second below the first, and so on) and command button. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. 4. To change the caption of the Userform, label and command button, click View, Properties Window and click on each control. 5. To show the Userform, place a command button on your worksheet and add the following code line: Private Sub CommandButton1_Click() UserForm1.Show vbModeless End Sub Explanation: by adding vbModeless, you can use the Userform and work in your worksheet at the same time.

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command button on the Userform. 6. Open the Visual Basic Editor. 7. In the Project Explorer, double click on UserForm1. 8. Double click on the command button. 9. Add the following code lines: Private Sub CommandButton1_Click() Dim i As Integer For i = 1 To 10 Controls("TextBox" & i).Value = Cells(i + 1, 1).Value Next i End Sub Explanation: With just a few lines of code, we can fill the text boxes with the phone numbers from the sheet. We used the & operator to concatenate (join) the elements. These code lines work because we didn't change the names of the text box controls (TextBox1, TextBox2, TextBox3, etc). To change the names of the controls, click View, Properties Window and click on each control. Result when you click the command button on the sheet:

Evita los errores de packaging Inspecciona artes en segundos Controla texto, imágenes, códigos de barra, códigos QR, impresión en braille, etc. arviscan.com

ABRIR

9/11 Completed! Learn much more about userforms > Go to Next Chapter: Range

CONTROLS COLLECTION • FREE EXCEL HELP • COPY RIGHT (C) 2010-2020 • ALL RIGHTS RESERVED MICROSOFT EXCEL TUTORIAL | HOW TO USE VBA & MACROS | EXCEL FORMULAS | FUNCTIONS IN EXCEL