Home Excel - Dicas Microsoft Excel VBA Excel VBA - Userforms e outros Excel planilha vba userform controles labels textboxes comboboxes

Excel planilha vba userform controles labels textboxes comboboxes

E-mail Imprimir PDF

-  Saberexcel o site das macros

Este artigo descreve como modificar UserForms programaticalmente no Excel de Microsoft. Ele inclui exemplos e Microsoft Visual Básico para Aplicações (VBA - Visual Basic Application ) macros que lhe mostram como tirar proveito das capacidades de UserForms e como usar o ActiveX ™ controles que são disponíveis para UserForms.

Uma introdução para os princípios básicos de UserForms descreve como expor UserForms, como ocultar temporariamente UserForms, e como despedir UserForms. Você também é mostrado como usar os eventos mais comuns que se associam com UserForms-o evento Inicializar, o evento de Clique, e o evento Terminar. Um ou vários dos exemplos seguintes demonstram como usar cada um dos ActiveX seguintes ™ controles em um UserForm:

  • TextBox control
  • CommandButton control
  • ListBox control
  • ComboBox control
  • Frame control
  • OptionButton control
  • CheckBox control
  • ToggleButton control
  • TabStrip control
  • MultiPage control
  • ScrollBar control
  • SpinButton control
  • RefEdit control
  • Image control

INTRODUCTION

This article describes how to use VBA to make changes in UserForms in Excel....
This article describes how to use VBA to make changes in UserForms in Excel.

top


Microsoft provides programming examples for illustration only, without warranty...

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

top

UserForm basics

How to display a UserForm

The syntax that is used to display a UserForm programmatically is the following:

UserFormName.Show

To display a UserForm that is named UserForm1, use the following code:

UserForm1.Show

You can load a UserForm into memory without actually displaying it. It may take a complex UserForm several seconds to appear. Because you can preload a UserForm into memory, you can decide when to incur this overhead. To load UserForm1 into memory without displaying it, use the following code:

Load UserForm1

To display the UserForm, you must use the Show method that was previously shown.

How to temporarily hide a UserForm

If you want to temporarily hide a UserForm, use the Hide method. You may want to hide a UserForm if your application involves moving between UserForms. To hide a UserForm, use the following code:

UserForm1.Hide

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

213747 (http://support.microsoft.com/kb/213747/ ) XL2000: How to move between custom UserForms with command buttons

How to remove a UserForm from memory

To remove a UserForm from memory, use the Unload statement. To unload a UserForm that is named UserForm1, use the following code:

Unload UserForm1

If you unload a UserForm in an event procedure that is associated with a UserForm or that is associated with a control on a UserForm (for example, you click a CommandButton control), you can use the "Me" keyword instead of the name of the UserForm. To use the "Me" keyword to unload a UserForm, use the following code:

Unload Me

How to use UserForm events

UserForms support many predefined events that you can attach VBA procedures to. When the event occurs, the procedure that you attached to the event runs. A single action that is performed by a user can initiate multiple events. Among the most frequently used events for a UserForm are the Initialize event, the Click event, and the Terminate event.

Note A Visual Basic module that contains an event procedure may be referred to as a module "behind" the UserForm. A module that contains event procedures is not visible in the Modules collection of the Microsoft Project Explorer window of the Visual Basic Editor. You must double-click the body of a UserForm to view the UserForm Code module.


How to trap UserForm events

To trap UserForm events, follow these steps:

Create a new workbook in Excel. 
On the Tools menu, point to Macro, and then click Visual Basic Editor
On the Insert menu, click UserForm to insert a UserForm in your workbook. 
Double-click the UserForm to display the Code window for the UserForm. 
In the module, type the following code:

 Private Sub UserForm_Click()
 
Me.Height = Int(Rnd * 500)
Me.Width = Int(Rnd * 750)
End Sub

 

 Private Sub UserForm_Initialize()
 M
e.Caption = "Events Events Events!"
  Me.BackColor = RGB(10, 25, 100)
 End Sub

  

Private Sub UserForm_Resize()
msg = "Width: " & Me.Width & Chr(10) & "Height: " & Me.Height
MsgBox prompt:=msg, Title:="Resize Event"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   Msg = "Now Unloading " & Me.Caption 
   
MsgBox prompt:=msg, Title:="QueryClose Event"
End Sub

  

Private Sub UserForm_Terminate()
msg = "Now Unloading " & Me.Caption
MsgBox prompt:=msg, Title:="Terminate Event"
End Sub

On the Run menu, click Run Sub/UserForm.

When the UserForm is first loaded, the macro uses the Initialize event to change the Caption property of the UserForm to "Events Events Events!" and the BackColor property to dark blue.

When you click the UserForm, you initiate the Click event. The Click event resizes the UserForm. Because you created a procedure for the Resize event, you receive two message boxes after you click the UserForm. The Resize event occurs two times because the code behind the Click event changes both the Width property and the Height property of the UserForm.

Closing the UserForm initiates the QueryClose event. The QueryClose event displays a message box that contains the caption that you gave the UserForm in the code for the Initialize event. You can use the QueryClose event when you want to perform a certain set of actions if the user closes the UserForm.

The Terminate event then generates a message box that states that the caption of the UserForm is UserForm1. The Terminate event occurs after the UserForm is removed from memory and the caption of the UserForm returns to its original state.

How to prevent a UserForm from being closed by using the Close button

When you run a UserForm, a Close button is added to the upper-right corner of the UserForm window. If you want to prevent the UserForm from being closed by using the Close button, you must trap the QueryClose event.

The QueryClose event occurs just before the UserForm is unloaded from memory. Use the CloseMode argument of the QueryClose event to determine how the UserForm is closed. The vbFormControlMenu value for the CloseMode argument indicates that the Close button was clicked. To keep the UserForm active, set the Cancel argument of the QueryClose event to True. To use the QueryClose event to prevent a UserForm from being closed by using the Close button, follow these steps:

1. Create a new workbook in Excel.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a CommandButton control to the UserForm.

5. Double-click the UserForm to display the Code window for the UserForm.

6. In the Code window, type the following code:

Private Sub CommandButton1_Click()
U
nload Me
End Sub

  

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
IF CloseMode = vbFormControlMenu Then
Cancel = True
Me.Caption = "Click the CommandButton to close Me!"
End If
End Sub

On the Run menu, click Run Sub/UserForm.

The UserForm is not closed when you click the Close button. You must click the CommandButton control to close the UserForm.

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

207714 (http://support.microsoft.com/kb/207714/ ) XL2000: Run-time errors using UserForms collection

211527 (http://support.microsoft.com/kb/211527/ ) XL2000: Cannot drag UserForm control onto a worksheet

211868 (http://support.microsoft.com/kb/211868/ ) XL2000: Error running macro that inserts control into UserForm

213582 (http://support.microsoft.com/kb/213582/ ) XL2000: Problems when you use macro to add control to UserForm

213583 (http://support.microsoft.com/kb/213583/ ) XL2000: Unable to show UserForms in other projects

213736 (http://support.microsoft.com/kb/213736/ ) XL2000: How to determine the key pressed along with mouse button

213744 (http://support.microsoft.com/kb/213744/ ) XL2000: How to temporarily hide a UserForm

213747 (http://support.microsoft.com/kb/213747/ ) XL2000: How to move between custom UserForms with command buttons

213749 (http://support.microsoft.com/kb/213749/ ) XL2000: How to use a UserForm for entering data

213768 (http://support.microsoft.com/kb/213768/ ) XL2000: How to dynamically resize a User Form

213774 (http://support.microsoft.com/kb/213774/ ) XL2000: How to create a Startup screen with a UserForm


top

VBA code

Excel includes fifteen different controls that you can use on UserForms. This section contains various examples that use these controls programmatically.

Note The VBA code that is included in this article does not contain examples that affect all the properties and events for the controls. If you have to, you can use the Properties window to see a list of the properties that are available for a control. To see a list of properties, on the View menu, click Properties Window.

How to use design mode to edit controls

When you use the Visual Basic Editor to design a dialog box, you are using design mode. In design mode, you can edit controls and you can change the properties of a control on a UserForm in the Properties window. To display the Properties window, on the View menu, click Properties Window.

Note Controls do not respond to events while you are in design mode. When you run a dialog box to display it the way that users see it, the program is in run mode. Changes that you make to the properties of a control in run mode are not retained when the UserForm is unloaded from memory.

Note Controls do respond to events in run mode.

How to refer to controls on a UserForm

How you refer to controls programmatically depends on the type of Visual Basic module sheet where you run the code. If the code is running from a General module, the syntax is the following:

UserFormName.Controlname.Property = Value

For example, if you want to set the Text property of a TextBox control that is named TextBox1 on a UserForm that is named UserForm1 to the value of Bob, use the following code:

UserForm1.TextBox1.Text = "Bob"

If the code is in a procedure that is initiated by an event of a control or by the UserForm, you do not have to refer to the name of the UserForm. Instead, use the following code:

TextBox1.Text = "Bob"

When you attach code to an object, the code is attached to one of the events of that object. In many of the examples in this article, you attach code to the Click event of the CommandButton object.


top

TextBox controls

TextBox controls are frequently used to gather input from a user. The Text property contains the entry that is made in a TextBox control.

How to use a TextBox control to validate a password

If you set the PasswordChar property of a TextBox control, it becomes a "masked-edit" control. Every character that is typed in the TextBox control is replaced visually by the character that you specify. To use a TextBox control to validate a password, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a TextBox control to the UserForm.

5. On the View menu, click Properties to make the Properties window visible.

6. In the PasswordChar property of the TextBox control, type *.

Note You are changing the value to an asterisk.

7. Add a CommandButton control to the UserForm.

8. Double-click the CommandButton control to open the Code window for the UserForm.

9. In the Code window, type the following code for the CommandButton1 Click event:

Private Sub CommandButton1_Click()
If TextBox1.Text <> "userform" Then
   
MsgBox "Password is Incorrect. Please reenter."

TextBox1.Text = ""
T
extBox1.SetFocus

Else

MsgBox "Welcome!"
Unload Me
End If 

End Sub

23. On the Run menu, click Run Sub/UserForm.
24. Type the password userform in the TextBox control.25. Click the CommandButton control.

For this example, the password is "userform". If you type an incorrect password, you receive a message box that states that your password is incorrect, the TextBox control is cleared, and then you can retype the password. When you type a correct password, you receive a welcome message, and the UserForm is closed.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

213555 (http://support.microsoft.com/kb/213555/ ) XL2000: No Data Validation property for UserForm TextBoxes


top

CommandButton controls

You can use a CommandButton control to start a VBA procedure. The VBA procedure is typically attached to the Click event of the CommandButton control. To use a CommandButton control that runs a procedure when the Click event occurs, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a CommandButton control to the UserForm.

5. Double-click the CommandButton control to display the Code window for the UserForm.

6. In the Code window, type the following code:

Private Sub CommandButton1_Click()

red = Int(Rnd * 255)
green = Int(Rnd * 255)
blue = Int(Rnd * 255)
CommandButton1.BackColor = RGB(red, green, blue)
End Sub

14. On the Run menu, click Run Sub/UserForm.

The background color of the CommandButton1 control changes every time that you click it.

For additional information about the CommandButton control, click the following article numbers to view the articles in the Microsoft Knowledge Base:

213572 (http://support.microsoft.com/kb/213572/ ) XL2000: Clicking Cancel button may not dismiss UserForm

213743 (http://support.microsoft.com/kb/213743/ ) XL2000: How to set the default command button on a UserForm


Back to the top

ListBox controls

The purpose of the ListBox control is to present the user with a list of items to select from. You can store the item list for a ListBox control on an Excel worksheet. To populate a ListBox control with a range of cells on a worksheet, use the RowSource property. When you use the MultiSelect property, you can set up a ListBox control to accept multiple selections.

How to obtain the currently selected item from the ListBox control

Use the Value property of a ListBox control to return the currently selected item. To return the currently selected item in a single select ListBox control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. In cells A1:A5 on Sheet1, type the values that you want to use to populate the ListBox control.

3. On the Tools menu, point to Macro, and then click Visual Basic Editor.

4. On the Insert menu, click UserForm to insert a UserForm in your workbook.

5. Add a ListBox control to the UserForm.

6. Double-click the ListBox control to display the Code window for the ListBox control.

7. In the Code window, type the following code for the ListBox1 Click event:

Private Sub ListBox1_Click()
MsgBox ListBox1.Value
End Sub

12. On the Run menu, click Run Sub/UserForm.

When you click an item in the list, a message box appears with the currently selected item.

How to obtain the selected items in a multiple select ListBox control

To determine the items that are selected in a multiple select ListBox control, you must loop through all the items in the list, and then query the Selected property. To return the currently selected items in a multiple select ListBox control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. In cells A1:A5 on Sheet1, type the values that you want to use to populate the ListBox control.

3. On the Tools menu, point to Macro, and then click Visual Basic Editor.

4. On the Insert menu, click UserForm to insert a UserForm in your workbook.

5. Add a ListBox control to the UserForm.

6. On the View menu, click Properties to see the Properties window.

7. Type the values that are indicated for the following ListBox control properties:

1.  Property Value

2. ----------- -----------------------

3. MultiSelect 1 - frmMultiSelectMulti

RowSource Sheet1!A1:A8

11. Add a CommandButton control to the UserForm.

12. Double-click the CommandButton control to display the Code window for the UserForm.

13. In the Code window, type the following code for the CommandButton1 Click event:

Sub CommandButton1_Click ()
'
Loop through the items in the ListBox.

For x = 0 to ListBox1.ListCount - 1 
    
If ListBox1.Selected(x) = True Then 
        
MgBox ListBox1.List(x)
    
End If
Next x

End Sub


27. On the Run menu, click Run Sub/UserForm.

28. Select one or more items in the list.

29. Click CommandButton1.

After you click CommandButton1, every item that you selected in the ListBox control appears in a separate message box. After all the selected items appear in a message box, the UserForm is automatically closed.

How to use the RowSource property to populate a ListBox control with cells on a worksheet

To use the RowSource property to populate a ListBox control from a range of cells on a worksheet, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. In cells A1:A5 on Sheet1, type the values that you want to use to populate the ListBox control.

3. On the Tools menu, point to Macro, and then click Visual Basic Editor.

4. On the Insert menu, click UserForm to insert a UserForm in your workbook.

5. Add a ListBox control to the UserForm.

6. Add a CommandButton control to the UserForm.

7. Double-click the CommandButton control to display the Code window for the UserForm.

8. In the Code window, type the following code for the CommandButton1 Click event:

Private Sub CommandButton1_Click()
ListBox1.RowSource = "=Sheet1!A1:A5"
End Sub

11. On the Run menu, click Run Sub/UserForm.

NoteListBox1 does not contain any values.

12. Click CommandButton1.

ListBox1 is populated with the values in cells A1:A5 on Sheet1.

How to populate a ListBox control with values in an array

This example shows you how to populate a ListBox control with an array variable. You must assign the values from the array to the ListBox control one item at a time. Typically, this process requires that you use a looping structure, such as a For…Next loop. To populate a ListBox control with an array variable, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a ListBox control to the UserForm.

5. On the Insert menu, click Module to insert a module sheet.

6. In the Code window, type the following code:

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Array("Maçãs", "Laranjas", "Pêras", "Bananas", "Melancias")

For Ctr = LBound(MyArray) To UBound(MyArray)
    UserForm1.ListBox1.AddItem MyArray(Ctr)
Next

UserForm1.Show
End Sub

On the Tools menu, click Macros, click PopulateListBox, and then click Run.

The PopulateListBox procedure builds a simple array, and then adds the items in the array to the ListBox control by using the AddItem method. Then, the UserForm appears.

How to use a horizontal range of cells on a worksheet to populate a ListBox control

If you set the RowSource property of a ListBox control to a horizontal range of cells, only the first value appears in the ListBox control.

To populate a ListBox control from a horizontal range of cells by using the AddItem method, follow these steps:
 Start Excel, and then open a new blank workbook.

In cells A1:E1 on Sheet1, type the values that you want to use to populate the ListBox control.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu, click UserForm to insert a UserForm in your workbook.

Add a ListBox control to the UserForm. 
On the Insert menu, click Module to insert a module sheet. 
In the Code window, type the following code:

Sub PopulateListWithHorizontalRange()
For Each x In Sheet1.Range("A1:E1")
    
UserForm1.ListBox1.AddItem x.Value
Next
UserForm1.Show
End Sub

On the Tools menu, click Macros, click PopulateListWithHorizontalRange, and then click Run.
The macro procedure loops through cells A1:E5 on Sheet1, adding the values to ListBox1 one at a time.

NoteListBox1 is not bound to cells A1:E5 on Sheet1.

How to return multiple values from a ListBox control that is bound to multiple columns of data

You can format ListBox controls to display more than one column of data. This means that the ListBox control displays more than one item on each line of the list. To return multiple values from the selected item in the list, follow these steps:
Start Excel, and then open a new blank workbook.
Type the following data in the cells that are indicated on Sheet1:

Collapse this table Expand this table

A1: Year

B1: Region

C1: Sales

A2: 1996

B2: North

C2: 140

A3: 1996

B3: South

C3: 210

A4: 1997

B4: North

C4: 190

A5: 1997

B5: South

C5: 195

On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu, click UserForm to insert a UserForm in your workbook.
Add a Label control to the UserForm.
Add a ListBox control to the UserForm.
Right-click the ListBox, and then click Properties.

ype or select the values that are indicated for the following properties of the ListBox control as listed in the following table:

Property Value
BoundColumn 1
ColumnCount 3
ColumnHeads True
RowSource Sheet1!A2:A5

Double-click the ListBox control to display the Code window for the ListBox control.
In the Code window, type the following code:

Private Sub ListBox1_Change()
Data As Range
Dim Val1 As String, Val2 As String, Val3 As String 
Set SourceRange = Range(ListBox1.RowSource)

Val1 = ListBox1.Value
Val2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value
Val3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value

Label1.Caption = Val1 & " " & Val2 & " " & Val3
End Sub

On the Run menu, click Run Sub/UserForm.

When you click an entry in the ListBox control, the label changes to display all three of the items in that entry.

How to remove all the items from a ListBox control that is bound to a worksheet

To remove all the items from a ListBox control that is bound to a worksheet, clear the value that is stored in the RowSource property. To remove items from a ListBox control that is bound to a worksheet, follow these steps:
Start Excel, and then open a new blank workbook.
In cells A1:A5 on Sheet1, type the values that you want to use to populate the ListBox control.
On the Tools menu, point to Macro, and then click Visual Basic Editor.
On the Insert menu, click UserForm to insert a UserForm in your workbook.
Add a ListBox control to the UserForm. 
Right-click the ListBox control, and then click Properties.
In the RowSource property, type Sheet1!A1:A5.

Add a CommandButton control to the UserForm.
Double-click the CommandButton control to display the Code window for the CommandButton control.
In the Code window, type the following code for the CommandButton1 Click event:

Private Sub CommandButton1_Click()
ListBox1.RowSource = ""
End Sub

15. On the Run menu, click Run Sub/UserForm.

The ListBoxcontrol that you added to the UserForm is populated with the values that you entered on Sheet1.

16. Click CommandButton1.

All the items are removed from ListBox1.

How to remove all the items from a ListBox control that is not bound to a worksheet

There is no single VBA command that removes all the items from a ListBox control if the list is not bound to a worksheet. To remove all the items from a ListBox control that is populated from a Visual Basic array, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a ListBox control to the UserForm.

5. On the Insert menu, click Module to insert a module sheet.

In the Code window, type the following code:

Sub PopulateListBox()
Dim MyArray As Variant
Dim Ctr As Integer
MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")
 
For Ctr = LBound(MyArray) To UBound(MyArray) 
       U
serForm1.ListBox1.AddItem MyArray(Ctr)
 
Next
UserForm1.Show
End Sub

Add a CommandButton control to the UserForm.

 Double-click the CommandButton control to display the Code window for the CommandButton control.
 In the Code window, type the following code for the CommandButton1 Click event:

 Private Sub CommandButton1_Click() 
   For i = 1 To ListBox1.ListCount   
       ListBox1.RemoveItem 0  
 
Next I 
E
nd Sub

On the Tools menu, click Macros, click PopulateListBox, and then click Run.

The ListBox control is populated, and then the UserForm appears.

Click CommandButton1.

All the items are removed from ListBox1.

For additional information about the ListBox control, click the following article numbers to view the articles in the Microsoft Knowledge Base:

161598 (http://support.microsoft.com/kb/161598/ ) OFF: How to add data to a ComboBox or ListBox in Excel or Word

211446 (http://support.microsoft.com/kb/211446/ ) XL2000: TextColumn property displays only the first column

211896 (http://support.microsoft.com/kb/211896/ ) XL2000: How to simulate combination List-Edit control for UserForms

211899 (http://support.microsoft.com/kb/211899/ ) XL2000: Problems setting column headings in ListBox control

213721 (http://support.microsoft.com/kb/213721/ ) XL2000: How to remove all items from a ListBox or ComboBox

213722 (http://support.microsoft.com/kb/213722/ ) XL2000: How to use the TextColumn property

213723 (http://support.microsoft.com/kb/213723/ ) XL2000: How to return values from a List box that displays multiple columns

213746 (http://support.microsoft.com/kb/213746/ ) XL2000: How to fill List Box control with multiple ranges

213748 (http://support.microsoft.com/kb/213748/ ) XL2000: How to populate one List Box based on another List Box

213752 (http://support.microsoft.com/kb/213752/ ) XL2000: Using the AddItem method causes an error when RowSource is data bound

213756 (http://support.microsoft.com/kb/213756/ ) XL2000: Using the RemoveItem method with ListBox or ComboBox control

213759 (http://support.microsoft.com/kb/213759/ ) XL2000: How to determine which items are selected in a List Box


top

ComboBox controls

You can use the ComboBox control as a drop-down list box, or as a combo box where you can select a value in a list or type a new value. The Style property determines if the ComboBox control acts as a drop-down list box or a combo box.

Note All the examples in the previous section for the ListBox control can also be applied to the ComboBox control, except for the "How to obtain the selected items in a multiple select ListBox control" example.

How to add a new item to the list if the ComboBox control is not bound to a worksheet

When you type a value that is not already in the list in the ComboBox control, you may want to add the new value to the list. To add the new value that you typed in the ComboBox control if the ComboBox control is not bound to the worksheet, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a ComboBox control to the UserForm.

5. On the Insert menu, click Module to insert a module sheet.

6. In the Code window, type the following code:

1. Sub PopulateComboBox()

2.

3. Dim MyArray As Variant

4. Dim Ctr As Integer

5. MyArray = Array("Apples", "Oranges", "Peaches", "Bananas", "Pineapples")

6.

7. For Ctr = LBound(MyArray) To Ubound(MyArray)

8. UserForm1.ComboBox1.AddItem MyArray(Ctr)

9. Next

10.

11. UserForm1.Show

12.

End Sub

19. Add a CommandButton control to the UserForm.

20. Double-click the CommandButton control to display the Code window for the CommandButton control.

21. In the Code window, type the following code for the CommandButton1 Click event:

1. Private Sub CommandButton1_Click()

2.

3. Dim listvar As Variant

4.

5. listvar = ComboBox1.List

6.

7. On Error Resume Next

8. ' If the item is not found in the list...

9. If IsError(WorksheetFunction.Match(ComboBox1.Value, listvar, 0)) Then

10. ' add the new value to the list.

11. ComboBox1.AddItem ComboBox1.Value

12. End If

13.

End Sub

35. On the Tools menu, click Macros, click PopulateListBox, and then click Run.

The ComboBox control is populated, and then the UserForm appears.

36. In the ComboBox control, type Mangoes (or any value that is not already in the list).

37. Click CommandButton1.

The new value that you typed now appears at the end of the list.

How to add a new item to the list if the ComboBox control is bound to a worksheet

When a user types a value that is not already in the list in the ComboBox control, you may want to add the new value to the list. To add the new value that you typed in the ComboBox control to the list, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. In cells A1:A5 on Sheet1, type the values that you want to use to populate the ComboBox control.

3. Select cells A1:A5 on Sheet1.

4. On the Insert menu, point to Name, and then click Define.

In the Names in workbook box, type ListRange , and then click OK. This creates the defined name ListRange. You can use the defined name ListRange to bind the RowSource property of the ComboBox control to the worksheet.

5. On the Tools menu, point to Macro, and then click Visual Basic Editor.

6. On the Insert menu, click UserForm to insert a UserForm in your workbook.

7. Add a ComboBox control to the UserForm.

8. In the Properties for ComboBox1, type Sheet1!ListRange as the RowSource property.

9. Add a CommandButton control to the UserForm.

10. Double-click the CommandButton control to display the Code window for the CommandButton control.

11. In the Code window, type the following code for the CommandButton1 Click event:

1. Private Sub CommandButton1_Click()

2.

3. Dim SourceData As Range

4. Dim found As Object

5.

6. Set SourceData = Range("ListRange")

7. Set found = Nothing

8. ' Try to find the value on the worksheet.

9. Set found = SourceData.Find(ComboBox1.Value)

10.

11. ' If the item is not found in the list...

12. If found Is Nothing Then

13. ' redefine ListRange.

14. SourceData.Resize(SourceData.Rows.Count + 1, 1).Name = "ListRange"

15. ' Add the new item to the end of the list on the worksheet.

16. SourceData.Offset(SourceData.Rows.Count, 0).Resize(1, 1).Value _

17. = ComboBox1.Value

18. ' Reset the list displayed in the ComboBox.

19. ComboBox1.RowSource = Range("listrange").Address(external:=True)

20. End If

21.

End Sub

33. On the Run menu, click Run Sub/UserForm.

The UserForm appears on Sheet1.

34. In the ComboBox control, type a value that is not already in the list.

35. Click CommandButton1.

The new item that you typed in the ComboBox control is added to the list, and the list that the ComboBox control is bound to is expanded to include cells A1:A6.

How to display the list of a ComboBox control when the UserForm appears

Sometimes, it may be useful to display the list of a ComboBox control when a UserForm first appears. The following example uses the Activate event of the UserForm. To display the list of a ComboBox control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. In cells A1:A5 on Sheet1, type the values that you want to use to populate the ComboBox control.

3. On the Tools menu, point to Macro, and then click Visual Basic Editor.

4. On the Insert menu, click UserForm to insert a UserForm in your workbook.

5. Add a ComboBox control to the UserForm.

6. In the Properties for ComboBox1, type Sheet1!A1:A5 as the RowSource property.

7. Double-click the UserForm to display the Code window for the UserForm.

8. In the Code window, type the following code for the CommandButton Click event:

1. Private Sub UserForm_Activate()

2.

3. ComboBox1.DropDown

4.

End Sub

13. On the Run menu, click Run Sub/UserForm.

The UserForm appears on Sheet1, and you can see the list for ComboBox1.

How to display the list of one ComboBox control when you make a selection in another ComboBox control

To automatically display the list of one ComboBox control when a choice is made in another ComboBox control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. In cells A1:A10 on Sheet1, type the values that you want to use to populate the ComboBox control.

3. On the Tools menu, point to Macro, and then click Visual Basic Editor.

4. On the Insert menu, click Module.

5. In the Code window for the module, type the following code:

1. Sub DropDown_ComboBox()

2.

3. UserForm1.ComboBox2.DropDown

4.

End Sub

10. On the Insert menu, click UserForm to insert a UserForm in your workbook.

11. Add a ComboBox control to the UserForm.

12. In the Properties for ComboBox1, type Sheet1!A1:A5 as the RowSource property.

13. Double-click the ComboBox control to open the Code window for the ComboBox control.

14. In the Code window for the ComboBox control, type the following code for the ComboBox Click event:

1. Private Sub ComboBox1_Click()

2.

3. Application.OnTime Now, "DropDown_ComboBox"

4.

End Sub

19. Add a second ComboBox control to the UserForm.

20. In the Properties for ComboBox2, type Sheet1!A6:A10 as the RowSource property.

21. On the Run menu, click Run Sub/UserForm.

When you click an item in the ComboBox1 list , the list for ComboBox2 automatically appears.

For additional information about the ComboBox control, click the following article numbers to view the articles in the Microsoft Knowledge Base:

161598 (http://support.microsoft.com/kb/161598/ ) OFF: How to add data to a ComboBox or ListBox in Excel or Word

211446 (http://support.microsoft.com/kb/211446/ ) XL2000: TextColumn property displays only the first column

211899 (http://support.microsoft.com/kb/211899/ ) XL2000: Problems setting column headings in ListBox control

213717 (http://support.microsoft.com/kb/213717/ ) XL2000: Run-time error using the DropDown method with a ComboBox

213718 (http://support.microsoft.com/kb/213718/ ) XL2000: How to display a ComboBox list when a UserForm Is displayed

213721 (http://support.microsoft.com/kb/213721/ ) XL2000: How to remove all items from a ListBox or ComboBox

213722 (http://support.microsoft.com/kb/213722/ ) XL2000: How to use the TextColumn property

213752 (http://support.microsoft.com/kb/213752/ ) XL2000: Using the AddItem method causes an error when RowSource Is data bound

213756 (http://support.microsoft.com/kb/213756/ ) XL2000: Using the RemoveItem method with ListBox or ComboBox control


Back to the top

Frame control

Use a Frame control to group logically related items in a UserForm. Frame controls are frequently used to group OptionButton controls.

How to loop through all the controls on a Frame control

To use a For Each…Next loop to access all the controls in a Frame control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a Frame control to the UserForm.

5. Add an OptionButton control to the Frame control.

Repeat this step to add two more OptionButton controls in the Frame control.

6. Double-click the Frame control to open the Code window for the Frame control.

7. In the Code window, type the following code for the Frame Click event:

1. Private Sub Frame1_Click()

2.

3. Dim Ctrl As Control

4.

5. For Each Ctrl In Frame1.Controls

6. Ctrl.Enabled = Not Ctrl.Enabled

7. Next

8.

End Sub

16. On the Run menu, click Run Sub/UserForm.

17. In the UserForm, click the Frame control.

The first time that you click the Frame control, all the controls in the Frame control are unavailable. If you click the Frame control again, the controls are available again.


top

OptionButton control

You can use groups of OptionButton controls to make one selection among a group of options. You can use either of the following techniques to group OptionButton controls:

  • Frame control
  • GroupName property

Note The On value, the Yes value, and the True value indicate that an OptionButton is selected. The Off value, the No value, and the False value indicate that an OptionButton is not selected.

How to determine the OptionButton control that is selected when the OptionButton controls are on a Frame control

When you group OptionButtons controls by using a Frame control, you can determine the OptionButton control that is selected by looping through all the controls in the Frame control and checking the Value property of each control. To determine the OptionButton control that is selected, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a Frame control to the UserForm.

5. Add an OptionButton control to the Frame control.

Repeat this step to add two more OptionButton controls in the Frame control.

6. Add a CommandButton control on the UserForm outside the Frame control.

7. Double-click the CommandButton control to display the Code window for the UserForm.

8. In the Code window, type the following code for the CommandButton1 Click event:

1. Private Sub CommandButton1_Click()

2.

3. For Each x In Frame1.Controls

4. If x.Value = True Then

5. MsgBox x.Caption

6. End If

7. Next

8.

End Sub

17. On the Run menu, click Run Sub/UserForm.

18. In the UserForm, click one OptionButton control, and then click CommandButton1.

A message box appears that contains the caption of the currently selected OptionButton control.

How to determine the OptionButton control that is selected

The purpose of the following example is to determine the OptionButton control that is selected in Group1. To create a UserForm that has two groups of OptionButton controls, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a Frame control to the UserForm.

5. Add an OptionButton control in the Frame control.

Repeat this step to add two more OptionButton controls in the Frame control.

6. For each OptionButton control, type Group1 in the GroupName property.

7. Repeat steps 4 and 5 to create a second Frame control that contains three OptionButton controls.

8. For each OptionButton control in the second Frame control, type Group2 in the GroupName property.

9. Add a CommandButton control on the UserForm outside the Frame controls.

10. Double-click the CommandButton control to display the Code window for the UserForm.

11. In the Code window, type the following code for the CommandButton1 Click event:

1. Private Sub CommandButton1_Click()

2.

3. Dim x As Control

4.

5. ' Loop through ALL the controls on the UserForm.

6. For Each x In Me.Controls

7. ' Check to see if "Option" is in the Name of each control.

8. If InStr(x.Name, "Option") Then

9. ' Check Group name.

10. If x.GroupName = "Group1" Then

11. ' Check the status of the OptionButton.

12. If x.Value = True Then

13. MsgBox x.Caption

14. Exit For

15. End If

16. End If

17. End If

18. Next

19.

End Sub

31. On the Run menu, click Run Sub/UserForm.

32. In the UserForm, click one OptionButton control in Group1, and then click CommandButton1.

A message box appears that contains the caption of the OptionButton control that is currently selected.

For additional information about OptionButton controls, click the following article number to view the article in the Microsoft Knowledge Base:

213724 (http://support.microsoft.com/kb/213724/ ) XL2000: Problems using TripleState property for option button


top

CheckBox control

You can use a CheckBox control to indicate a true or false value. A CheckBox control that appears with a check mark in it indicates a value of True. A CheckBox that appears with no check mark indicates a value of False. If the value of the TripleState property is True, a CheckBox control can also have a value of Null. A CheckBox control that has a value of Null appears to be unavailable.

Note The On value, the Yes value, and the True value indicate that a CheckBox control is selected. The Off value, the No value, and the False value indicate that a CheckBox control is cleared.

How to check the value of a CheckBox control

To use the Value property to return the current value of a CheckBox control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a CheckBox control to the UserForm.

5. In the Properties list for CheckBox1, select True as the TripleState property.

6. Double-click the CheckBox control to display the Code window for the CheckBox control.

7. In the Code window, type the following code for the CheckBox1 Change event:

1. Private Sub CheckBox1_Change()

2.

3. Select Case CheckBox1.Value

4. Case True

5. CheckBox1.Caption = "True"

6. Case False

7. CheckBox1.Caption = "False"

8. Case Else

9. CheckBox1.Caption = "Null"

10. End Select

11.

End Sub

19. On the Run menu, click Run Sub/UserForm.

When you click the CheckBox control, the caption of the CheckBox control changes to reflect the current value.


top

ToggleButton control

A ToggleButton control has the same appearance as a CommandButton control until you click it. When you click a ToggleButton control, it appears to be pressed or pushed down. The Value property of a ToggleButton control is True when the button is selected and False when the button is not selected. If the value of the TripleState property is True, a ToggleButton control can also have a value of Null. A ToggleButton control that has a value of Null appears to be unavailable.

Note The On value, the Yes value, and the True value indicate that a ToggleButton control is selected. The Off value, the No value, and the False value indicate that a ToggleButton control is not selected.

How to obtain the value of a ToggleButton control

To obtain the value of a ToggleButton control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a ToggleButton control on the UserForm.

5. Add a Label control to the UserForm.

6. Double-click the ToggleButton control to open the Code window for the ToggleButton control.

7. In the Code window, type the following code for the ToggleButton1Click event:

1. Private Sub ToggleButton1_Click()

2.

3. If ToggleButton1.Value = True Then

4. ' Set UserForm background to Red.

5. Me.BackColor = RGB(255, 0, 0)

6. Else

7. ' Set UserForm background to Blue.

8. Me.BackColor = RGB(0, 0, 255)

9. End If

10.

End Sub

18. On the Run menu, click Run Sub/UserForm.

When you click the ToggleButton control, the background color of the UserForm changes.

How to create a group of mutually exclusive ToggleButton controls

This example uses the MouseUp event to set a variable and calls the ExclusiveToggleButtons procedure. The ExclusiveToggleButtons procedure determines the ToggleButton control that is selected, and then cancels the others. To create a group of mutually exclusive ToggleButton controls, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click Module.

4. In the Code window for the module, type the following code:

1. ' Variable that holds the name of the ToggleButton that was clicked.

2. Public clicked As String

3.

4. Sub ExclusiveToggleButtons()

5.

6. Dim toggle As Control

7.

8. ' Loop through all the ToggleButtons on Frame1.

9. For Each toggle In UserForm1.Frame1.Controls

10.

11. ' If Name of ToggleButton matches name of ToggleButton

12. ' that was clicked...

13. If toggle.Name = clicked Then

14. '...select the button.

15. toggle.Value = True

16. Else

17. '...otherwise clear the selection of the button.

18. toggle.Value = False

19. End If

20. Next

21.

End Sub

26. On the Insert menu, click UserForm to insert a UserForm in your workbook.

27. Add a Frame control to the UserForm.

28. Add a ToggleButton control in the Frame control.

Repeat this step to add two more ToggleButton controls in the Frame control.

29. Double-click the Frame control to display the Code window for the UserForm.

30. In the Code window for the module, type the following code for the ToggleButton MouseUp event:

1. Private Sub ToggleButton1_MouseUp(ByVal Button As Integer, _

2. ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

3.

4. clicked = ToggleButton1.Name

5. Application.OnTime Now, "ExclusiveToggleButtons"

6.

7. End Sub

8.

9. Private Sub ToggleButton2_MouseUp(ByVal Button As Integer, _

10. ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

11.

12. clicked = ToggleButton2.Name

13. Application.OnTime Now, "ExclusiveToggleButtons"

14.

15. End Sub

16.

17. Private Sub ToggleButton3_MouseUp(ByVal Button As Integer, _

18. ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

19.

20. clicked = ToggleButton3.Name

21. Application.OnTime Now, "ExclusiveToggleButtons"

22.

End Sub

53. On the Run menu, click Run Sub/UserForm.

When you click a ToggleButton control, the previously selected ToggleButton control is canceled.


top

TabStrip control

Use a TabStrip control to view different sets of information for a set of controls.

How to control a TabStrip control programmatically

To change the BackColor property of an Image control based on the tab that is selected, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a TabStrip control to the UserForm.

5. Add an Image control that covers the base of the TabStrip control, but that does not cover the tabs.

6. In the Properties pane for Image1, type &H000000FF& in the BackColor property.

7. Double-click the TabStrip control to open the Code window for the TabStrip control.

8. In the Code window, type the following code for the TabStrip1 Change event:

1. Private Sub TabStrip1_Change()

2.

3. Dim i As Integer

4.

5. i = TabStrip1.SelectedItem.Index

6. Select Case i

7. Case 0

8. ' If Tab1 is selected, change the color of Image control to Red.

9. Image1.BackColor = RGB(255, 0, 0)

10. Case 1

11. ' If Tab2 is selected, change the color of Image control to Green.

12. Image1.BackColor = RGB(0, 255, 0)

13. End Select

14.

End Sub

23. On the Run menu, click Run Sub/UserForm.

The color of the Image control changes depending on the page in the TabStrip control that is active.

For additional information about the TabStrip control, click the following article number to view the article in the Microsoft Knowledge Base:

213254 (http://support.microsoft.com/kb/213254/ ) XL2000: How to use the TabStrip control on a UserForm


top

MultiPage control

Use a MultiPage control to work with a lot of information that can be sorted into several categories. A MultiPage control is made up of one or more Page objects that each contain a different set of controls. You can set the active page programmatically by setting the Value property of the MultiPage control.

How to control a MultiPage control programmatically

To add a MultiPage control and control it by using a macro, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a MultiPage control to the UserForm.

5. Add a Label control to Page1 on the MultiPage control.

6. Add a TextBox control to Page1 on the MultiPage control.

7. On the MultiPage control, click Page2, and then repeat steps 5 and 6 to add a Label control and a TextBox control.

8. Double-click the MultiPage control to open the Code window for the MultiPage control.

9. In the Code window, type the following code for the MultiPage1 Change event:

1. Private Sub MultiPage1_Change()

2.

3. Select Case MultiPage1.Value

4. ' If activating Page1...

5. Case 0

6. Label1.Caption = TextBox2.Text

7. TextBox1.Text = ""

8. ' If activating Page2...

9. Case 1

10. Label2.Caption = TextBox1.Text

11. TextBox2.Text = ""

12. End Select

13.

End Sub

23. In the Code window, type the following code for the UserForm Initialize event:

1. Private Sub UserForm_Initialize()

2.

3. ' Force Page1 to be active when UserForm is displayed.

4. MultiPage1.Value = 0

5. Label1.Caption = ""

6.

End Sub

30. On the Run menu, click Run Sub/UserForm.

In the TextBox control on Page1, type Test. When you click the Page2 tab, TextBox2 is cleared, and the caption of Label2 changes to the entry that you made in TextBox1 on Page1 ("Test").

How to create a wizard interface by using a MultiPage control

When a task requires several incremental steps, a wizard interface can be very effective. You can use the MultiPage control to create a wizard interface instead of using multiple UserForms. This example manipulates a MultiPage control that has three pages. A procedure that is attached to the Initialize event of the UserForm disables Page2 and Page3, and forces Page1 of the MultiPage control to be active.

Note When you index the pages of a MultiPage control by using the Pages collection, the first page in the collection is page zero. This procedure also sets the caption of the CommandButton controls and disables the <Back button.

Note The procedure that is assigned to the Click event of CommandButton1 controls the functionality of the <Back button. The procedure that is assigned to the Click event of CommandButton2 controls the functionality of the Next> button. To create a wizard interface by using a MultiPage control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a MultiPage control to the UserForm.

5. Right-click the Page1 tab, and then click New Page to add Page3 to the MultiPage control.

6. Add a CommandButton control on the UserForm that is not on the MultiPage control.

Repeat this step to add a second CommandButton control on the UserForm.

7. Double-click the UserForm to open the Code window for the UserForm.

8. In the Code window, type the following code for the UserForm Initialize event:

1. Private Sub UserForm_Initialize()

2.

3. With MultiPage1

4. ' The next 2 lines disable Page2 & Page3.

5. .Pages(1).Enabled = False

6. .Pages(2).Enabled = False

7. ' Make Page1 the active page.

8. .Value = 0

9. End With

10.

11. ' Set the caption on the CommandButtons.

12. CommandButton1.Caption = "<Back"

13. CommandButton1.Enabled = False

14. CommandButton2.Caption = "Next>"

15.

16. End Sub

17.

18. ' Procedure for the "<Back" button

19. Private Sub CommandButton1_Click()

20. Select Case MultiPage1.Value

21. Case 1 ' If Page2 is active...

22. With MultiPage1

23. .Pages(0).Enabled = True ' Enable Page1.

24. .Value = MultiPage1.Value - 1 ' Move back 1 page.

25. .Pages(1).Enabled = False ' Disable Page2.

26. End With

27. CommandButton1.Enabled = False ' Disable Back button.

28.

29. Case 2 ' If Page3 is active...

30. With MultiPage1

31. .Pages(1).Enabled = True ' Enable Page2.

32. .Value = MultiPage1.Value - 1 ' Move back 1 page.

33. .Pages(2).Enabled = False ' Disable Page3.

34. CommandButton2.Caption = "Next>"

35. End With

36. End Select

37.

38. End Sub

39.

40. ' Procedure for the "Next>" button

41. Private Sub CommandButton2_Click()

42.

43. Select Case MultiPage1.Value

44. Case 0 ' If Page1 is active...

45. With MultiPage1

46. .Value = MultiPage1.Value + 1 ' Move forward 1 page.

47. .Pages(1).Enabled = True ' Enable Page2.

48. .Pages(0).Enabled = False ' Disable Page1.

49. End With

50. CommandButton1.Enabled = True ' Enable Back button.

51.

52. Case 1 ' If Page2 is active...

53. With MultiPage1

54. .Value = MultiPage1.Value + 1 ' Move forward 1 page.

55. .Pages(2).Enabled = True ' Enable Page3.

56. .Pages(1).Enabled = False ' Disable Page2.

57. End With

58. CommandButton2.Caption = "Finish" ' Change Next button to Finish.

59.

60. Case 2 ' If Page3 is active...

61. MsgBox "Finished!" ' User is Finished.

62. Unload Me ' Unload the UserForm.

63. End Select

64.

65. End Sub

74. On the Run menu, click Run Sub/UserForm.

When you click Next>, Page2 is activated and the <Back button becomes available. When you click Next> a second time, Page3 is activated and the caption for CommandButton2 changes to "Finish".


top

ScrollBar control

You can use a ScrollBar control when you want to change the value that is displayed by another control, such as a Label control.

How to change a Label control that is based on the value of a ScrollBar control

To change the Caption property of a Label control to the current setting of the Value property of a ScrollBar control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a ScrollBar control to the UserForm.

5. Add a Label control to the UserForm.

6. Double-click the ScrollBar control to open the Code window for the ScrollBar control.

7. In the Code window, type the following code for the ScrollBar1 Change event:

1. Private Sub ScrollBar1_Change()

2.

3. Label1.Caption = ScrollBar1.Value

4.

End Sub

12. On the Run menu, click Run Sub/UserForm.

When you scroll by using the ScrollBar control, Label1 is updated with the current value of the ScrollBar control.


top

SpinButton control

A SpinButton control, like a ScrollBar control, is frequently used to increment or to decrement the value of another control, such as a Label control. The SmallChange property determines how much the value of a SpinButton control changes when it is clicked.

How to add a SpinButton control that increments or decrements a date that is stored in a TextBox control

To add a SpinButton control that increments or decrements a date that is stored in a TextBox control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a SpinButton control to the UserForm.

5. Add a TextBox control to the UserForm.

6. Double-click the SpinButton control to open the Code window for the SpinButton control.

7. In the Code window, type the following code for the SpinButton1 SpinUp event:

1. Private Sub SpinButton1_SpinUp()

2.

3. TextBox1.Text = DateValue(TextBox1.Text) + 1

4.

5. End Sub

13. In the Code window, type the following code for the SpinButton1 SpinDown event:

1. Private Sub SpinButton1_SpinDown()

2.

3. TextBox1.Text = DateValue(TextBox1.Text) - 1

4.

5. End Sub

19. In the Code window, type the following code for the UserForm Initialize event:

1. Private Sub UserForm_Initialize()

2.

3. TextBox1.Text = Date

4.

5. End Sub

25. On the Run menu, click Run Sub/UserForm.

When the UserForm appears, the current date appears in TextBox1. When you click the SpinButton control, the date is incremented or decremented by one day.

In this example, if you change the SmallChange property of SpinButton1, you do not affect the number of days the entry in TextBox1 is changed by when you click SpinButton1. The number of days is determined only by the procedure that you attached to the SpinUp event and the SpinDown event of SpinButton1.

For additional information about the SpinButton control, click the following article number to view the article in the Microsoft Knowledge Base:

213224 (http://support.microsoft.com/kb/213224/ ) XL2000: Visual Basic example for using a Spin Button with a date


top

RefEdit control

The RefEdit control imitates the behavior of the reference boxes that are built into Excel. You can use the Value property to obtain the current cell address that are stored in a RefEdit control.

How to populate a range of cells based on the range that you select by using the RefEdit control

To use the RefEdit control to populate cells, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add a RefEdit control to the UserForm.

5. Add a CommandButton control to the UserForm.

6. Double-click the CommandButton control to open the Code window for the CommandButton control.

7. In the Code window, type the following code for the CommandButton1 Click event:

1. Private Sub CommandButton1_Click()

2.

3. Dim MyRange As String

4. MyRange = RefEdit1.Value

5. Range(MyRange).Value = "test"

6. Unload Me

7.

End Sub

15. On the Run menu, click Run Sub/UserForm.

The UserFormappears.

16. Click the button in the RefEdit control.

Notice that the UserForm collapses.

17. Select a range of cells such as A1:A5, and then click the button in the RefEdit control to expand the UserForm.

18. Click CommandButton1.

The UserForm closes and the cells that you selected now contain the word "test".

For additional information about the RefEdit control, click the following article number to view the article in the Microsoft Knowledge Base:

213776 (http://support.microsoft.com/kb/213776/ ) XL2000: How to use the RefEdit control with a UserForm


top

Image control

The purpose of the Image control is to display a picture on a UserForm. To assign a picture to an Image control at run time, use the LoadPicture function.

How to load a picture into an Image control

To insert an Image control that prompts you to select a picture to load when you click the Image control, follow these steps:

1. Start Excel, and then open a new blank workbook.

2. On the Tools menu, point to Macro, and then click Visual Basic Editor.

3. On the Insert menu, click UserForm to insert a UserForm in your workbook.

4. Add an Image control on the UserForm.

5. Double-click the Image control to open the Code window for the Image control.

6. In the Code window, type the following code for the Image1 Click event:

1. Private Sub Image1_Click()

2.

3. Dim fname As String

4.

5. ' Display the Open dialog box.

6. fname = Application.GetOpenFilename(filefilter:= _

7. "Bitmap Files(*.bmp),*.bmp", Title:="Select Image To Open")

8.

9. ' If you did not click Cancel...

10. If fname <> "False" Then

11.

12. ' Load the bitmap into the Image control.

13. Image1.Picture = LoadPicture(fname)

14.

15. ' Refresh the UserForm.

16. Me.Repaint

17. End If

18.

19. End Sub

26. On the Run menu, click Run Sub/UserForm.

The UserForm appears.

27. Click the Image control.

When you click the Image control, the Select Image To Open dialog box appears, and then you can select a bitmap file to insert into the control.

For additional information about the Image control, click the following article number to view the article in the Microsoft Knowledge Base:

213732 (http://support.microsoft.com/kb/213732/ ) XL2000: Using the LoadPicture function with an Image control

Back to the top

Additional information

The Object Browser

A complete list of all the properties and methods for a specific command is available in the Object Browser. To find this information in Excel, follow these steps:

1. On the Tools menu, point to Macro, and then click Visual Basic Editor.

2. On the View menu, click Object Browser.

3. In the Search Text box, type the name of the control that you want, and then click Search.

For more information about how to use the Object Browser, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Object Browser in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For additional information about how to install Microsoft Excel Help on your computer, click the following article number to view the article in the Microsoft Knowledge Base:

231946 (http://support.microsoft.com/kb/231946/ ) OFF2000: How to add/remove a single Office program or component

top

 

APPLIES TO

  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Office Excel 2003

top

Keywords:

kbprogramming kbfunctions kbhowtomaster kbhowto KB829070


 

Última atualização em Ter, 23 de Agosto de 2011 10:11  

Adicionar comentário

"Jamais considere seus estudos como uma obrigação, mas como uma oportunidade invejável para aprender a conhecer a influência libertadora da beleza do reino do espírito, para seu próprio prazer pessoal e para proveito da comunidade." Albert Einstein


Código de segurança
Atualizar

Pesquisa Google SaberExcel

Publicidade Google

Publicidade

Rastreamento Correios

Digite o número do SEDEX conforme o exemplo:
Correios do Brasil

Assinatura SaberExcel

Google Associados

Depoimentos

Visitantes SaberExcel

Excel VBA Estudos®
mod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_countermod_vvisit_counter
mod_vvisit_counterHoje4911
mod_vvisit_counterOntem4316
mod_vvisit_counterEsta Semana19358
mod_vvisit_counterSemana passada36487
mod_vvisit_counterEsse mês33087
mod_vvisit_counterMês passado153723
mod_vvisit_counterTodos10455916
Aprenda MS Excel VBA

Adicione Saberexcel Favoritos

 
 

Aprenda tudo sobre o Aplicativo Microsoft Excel VBA

Aprenda tudo sobre o Aplicativo Microsoft Excel VBA(Visual Basic Application), sozinho, com baixo custo, praticando com os produtos didáticos Saberexcel,


   Sobre as WorksheetFunctions Funções de Planilhas que retornam valores do VBA