VBA Tutorial Login  

Free! Excel VBA Online Tutorial

   

Excel VBA

Excel UserForm Controls - Frame, MultiPage and TabStrip; group OptionButtons; create a wizard using MultiPage control

User Rating:  / 23
PoorBest 
Details

 

UserForm Controls - Frame, MultiPage and TabStrip

 

Also refer "2. UserForm and Controls - Properties" for properties common to the UserForm and most Controls

Note: In below given examples, vba codes are required to be entered in the Code Module of the UserForm, unless specified otherwise. 


_____________________________________________________________________________


Excel VBA UserForms and Controls

 

1. UserForms in Excel VBA - UserForm Basics; Add Controls dynamically at run-time using the Add Method; UserForm Controls (A Snapshot).

2. UserForm and Controls - Properties.

3. UserForm Controls - Label, TextBox and CommandButton.

4. UserForm Controls - ComboBox and ListBox.

5. UserForm Controls - CheckBox, OptionButton and ToggleButton.

6. UserForm Controls - Frame, MultiPage and TabStrip

7. UserForm Controls - ScrollBar and SpinButton.

8. UserForm Controls - Image and RefEdit.

_____________________________________________________________________________ 


 

Frame Control

 

Frames are used to group controls that work together, are related to each other or have some commonality, in a UserForm. They also enhance the UserForm's layout by bunching and organizing a related set of items. For example, in a BioData Form, the physical attributes like height, complexion, weight and hair color can be grouped in a particular Frame. Frames are particularly useful to group two or more OptionButtons. Primarily, Frames are used for 2 purposes: (i) to group related Controls in a UserForm to visually enhance and organize the Form's layout; and (ii) to group OptionButtons to set their behaviour - they become mutually exclusive within a Frame and selecting one OptionButton will de-select all other OptionButtons within that Frame only. Using a Frame: First add the Frame in a UserForm and then add Controls to the Frame.

 

 

Example 1: Determine name and caption of all enabled OptionButtons in a Frame

 

Private Sub CommandButton1_Click()
'determine name and caption of all enabled OptionButtons in a Frame


Dim ctrl As Control


For Each ctrl In Frame1.Controls

If TypeOf ctrl Is MSForms.OptionButton Then

If ctrl.Enabled = True Then

MsgBox ctrl.Name & " is an enabled OptionButton with caption " & ctrl.Caption

End If

End If

Next


End Sub

 

 

 

 

Example 2: Using Controls in a Frame with vba code (refer Image 25):

 

 

Private Sub UserForm_Initialize()
'Set properties of Controls on initialization of UserForm.


Dim i As Integer
Dim myArray As Variant


'enter value in "Name" TextBox of UserForm:
Me.txtName.Value = "Enter Your Name"


'set Caption for the 2 Frames:
Me.fraPhyAttr.Caption = "Physical Attributes"
Me.fraEduExp.Caption = "Education & Experience"


'populate ListBox of "Age" in the UserForm:
With Me.lstAge

For i = 1 To 100

.AddItem i & " yrs"

Next i

End With


'populate ListBox of "Height" in the Frame "Physical Attributes":

With Me.fraPhyAttr.ListBox1

For i = 140 To 200

.AddItem i & " cms"

Next i

End With


'populate ListBox of "Weight" in the Frame "Physical Attributes":
With Me.fraPhyAttr.ListBox2

For i = 80 To 250

.AddItem i & " lbs"

Next i

End With


'populate ListBox "Field of Work" in the frame "Education & Experience":
myArray = Array("Finance", "Banking", "Medical", "Engineering", "Marketing", "Management", "Airlines", "Others")
Me.fraEduExp.lstWorkField.List = myArray


'populate ListBox of "Exp in Years" in the Frame "Education & Experience":
With Me.fraEduExp.lstExpYrs

For i = 1 To 50

.AddItem i & " yrs"

Next i

End With


'Group OptionButtons:
With Me.fraPhyAttr

optDark.GroupName = "Complexion"
optWheatish.GroupName = "Complexion"

optFair.GroupName = "Complexion"


optBlack.GroupName = "Hair"
optBlonde.GroupName = "Hair"
optBrunette.GroupName = "Hair"
optOther.GroupName = "Hair"

End With


Me.fraEduExp.optGraduate.GroupName = "EduLevel"
Me.fraEduExp.optPostGrad.GroupName = "EduLevel"
Me.fraEduExp.optProfessional.GroupName = "EduLevel"


'enter value in "University/Institution" TextBox of the Frame "Education & Experience":
Me.fraEduExp.txtUniversityInstitution.Value = "Enter Name"


End Sub

 

 

 

Private Sub txtName_Enter()


'on selection, clears "Name" TextBox of UserForm
Me.txtName.Value = ""


End Sub

 

 

 

Private Sub txtUniversityInstitution_Enter()


'on selection, clears "University/Institution" TextBox of the Frame "Education & Experience"
Me.fraEduExp.txtUniversityInstitution.Value = ""


End Sub

 

 

------------------------------------------------------------------------------------------------------------------

 

MultiPage Control

 

A MultiPage control comprises of one or more Page objects, each containing its own set of controls. MultiPage is best used when you wish to handle a large amount of data that can be classified into multiple categories, wherein a separate Page can be created for each category. All controls that are added to a Page in a MultiPage control, are contained in and become a part of that Page which distinguishes the controls from those which are otherwise a part of the UserForm. A MultiPage control has several Pages, wherein selecting a Page makes it the current one (making it visible) while hiding the others. Each Page of a MultiPage control has its own tab order. The Pages are numbered from 0, and to select the first Page in a MultiPage control, use the code: MultiPage1.Value = 0. By default, a MultiPage control has 2 Pages. To Add Pages, right-click on the tab and select New Page ((select Delete Page to delete a Page). Note: Each Page of a MultiPage control will separately group two or more OptionButtons, irrespective of whether all OptionButtons (across all pages) have the same GroupName.

 

 

Dynamically Add/Remove a Page:

 

Using the Add Method: Syntax: Set m = MultiPage1.Pages.Add(pageName, pageCaption, pageIndex). pageIndex (optional) is an integer which specifies the position for the Page to be inserted, starting from 0 for the first position/Page. pageName sets the Name for the Page, pageCaption sets the Caption. Both are optional to specify.

 

Set m = MultiPage1.Pages.Add("Page5", "NewPage", 1) - this code adds a new Page with name Page5 and Caption NewPage, as the second Page (viz. second position in the page order).

 

MultiPage1.Pages.Add "Page3";    MultiPage1.Pages(2).Caption = "NewPage"   -   these 2 codes add a new (third) Page with name Page3 and set its Caption to NewPage.

 

MultiPage1.Pages.Add - this code simply adds a new Page.

 

 

To Remove a Page - Syntax: MultiPage1.Pages.Remove (pageIndex). Example: MultiPage1.Pages.Remove (1) - this code removes the second Page.

 

 

 

Dynamically Access an individual Page:

 

To change or set the properties of a Page at Run-time, we need to identify that Page in the MultiPage control, which can be done in different ways. To access an individual Page in a MultiPage control, the following methods can be used (refer Images 26a & 26b and below Example of vba codes):

 

1. Numeric Index (using the Pages collection): Index 0 refers to the first page, Index 1 to the second page, and so on. Code to set Caption - MultiPage1.Pages(Index).Caption

 

2. Item Method (using the Pages collection): Item Index 0 refers to the first page, Item Index 1 to the second page , and so on. Code to set Caption - MultiPage1.Pages.Item(ItemIndex).Caption

 

3. Page Name: Code to set Caption - MultiPage1.Pages("PageName").Caption or MultiPage1.Pages.Item("PageName").Caption

 

4. Page Object: Code to set Caption - MultiPage1.PageName.Caption

 

5. SelectedItem Property: Code to set Caption - MultiPage1.SelectedItem.Caption

 

 

Example 3: Change properties of each Page of a MultiPage control, by using different methods of Page Selection. Refer Image 26a before the code is run (ie. MultiPage is per Design-time) and refer Image 26b which shows the changed Captions (Run-time) after running the below vba code.

 


 

Private Sub UserForm_Activate()
'change/set properties of each Page of a MultiPage control, by using different methods of Page Selection.


'using Numeric Index: sets Caption for the first Page:
MultiPage1.Pages(0).Caption = "Australia"


'using Item Method: sets Caption for the second Page:
MultiPage1.Pages.Item(1).Caption = "Canada"


'using Page Name: sets Caption for the third Page, whose name is Page3:
MultiPage1.Pages("Page3").Caption = "France"
'alternate code: MultiPage1.Pages.Item("Page3").Caption = "France"


'using Page Object: sets Caption for the fourth Page, whose name is Page4:
MultiPage1.Page4.Caption = "Germany"


'using SelectedItem Property: sets Caption for the fifth Page, which is the selected/active page:

'selects the fifth Page
MultiPage1.Value = 4
'sets Caption for the selected page
MultiPage1.SelectedItem.Caption = "USA"

End Sub

 

 

 

 

Create a wizard using a Single UserForm and MultiPage control

 

In case you wish to accept data sequentially (ie. one step after another), then instead of using multiple UserForms it is better to use a MultiPage control to create multiple Pages in a Single UserForm. The next Page can be made accessible only after the previous Page has been filled and entered. We show how to create a wizard interface comprising of 4 Pages, as below.

 




 

Example 4: Refer Images 27a to 27d which show the 4 Pages in the MultiPage wizard. Below is the set of vba codes for these (to be entered in the Code Module of the UserForm):

 

Private Sub UserForm_Initialize()
'set properties of each Page of the MultiPage control


Dim i As Integer


'set Caption for each Page:
MultiPage1.Pages(0).Caption = "Name"
MultiPage1.Pages(1).Caption = "Gender & Age"
MultiPage1.Pages(2).Caption = "Personal"
MultiPage1.Pages(3).Caption = "Professional"


'set Caption for Frames in fourth Page:
MultiPage1.Pages(3).Frame1.Caption = "Field of Work"
MultiPage1.Pages(3).Frame2.Caption = "Education Level"


'populate ListBox "Age" in second Page:
With MultiPage1.Pages(1).lstAge

For i = 1 To 100

.AddItem i & " yrs"

Next i

End With


'populate ComboBox "Residing Country" in third Page:
myArray = Array("Argentina", "Australia", "Brazil", "China", "France", "Germany", "Greece", "HongKong", "India", "Italy", "Japan", "Russia", "Singapore", "Spain", "Switzerland", "UK", "USA")
MultiPage1.Pages(2).cmbCountry.List = myArray


MultiPage1.Pages(0).Enabled = True
MultiPage1.Pages(1).Enabled = False
MultiPage1.Pages(2).Enabled = False
MultiPage1.Pages(3).Enabled = False
'select first page:
MultiPage1.Value = 0


End Sub

 

 

 

Private Sub MultiPage1_Change()


'set properties of Previous, Next, Cancel & Save Buttons. Note: these buttons are created outside the MultiPage control.
Select Case MultiPage1.Value

'First Page:

Case 0

cmdPrevious.Enabled = False

cmdNext.Enabled = True

cmdSave.Enabled = False

'Last Page:

Case MultiPage1.Pages.Count - 1

cmdPrevious.Enabled = True

cmdNext.Enabled = False

cmdSave.Enabled = True

'Other Pages:

Case Else

cmdPrevious.Enabled = True

cmdNext.Enabled = True

cmdSave.Enabled = False

End Select


End Sub

 

 

 

Private Sub cmdSave_Click()
'set properties for CommandButton Save. Note: this code does not provide how/where MultiPage data will be saved, but only the conditions under which data will get stored.


'Last Page - atleast one OptionButton should be selected in each Frame:
With MultiPage1.Pages(MultiPage1.Pages.Count - 1)

If optAdmin.Value = False And optMktg.Value = False And optOps.Value = False And optOther1.Value = False Then

MsgBox "Please select Field of Work"

Exit Sub

ElseIf optGrad.Value = False And optPostGrad.Value = False And optProff.Value = False And optOther2.Value = False Then

MsgBox "Please select Education Level"

Exit Sub

End If

End With


End Sub

 

 

 

Private Sub cmdPrevious_Click()
'set properties for CommandButton Previous


Select Case MultiPage1.Value

Case 1

MultiPage1.Pages(1).Enabled = False

MultiPage1.Pages(0).Enabled = True

MultiPage1.Value = 0

Case 2

MultiPage1.Pages(2).Enabled = False

MultiPage1.Pages(1).Enabled = True

MultiPage1.Value = 1

Case 3

MultiPage1.Pages(3).Enabled = False

MultiPage1.Pages(2).Enabled = True

MultiPage1.Value = 2

End Select


End Sub

 

 

 

Private Sub cmdNext_Click()
'set properties for CommandButton Next


Select Case MultiPage1.Value

'Name is required to be entered to proceed to next Page:
Case 0

If txtName.Value = "" Then

MsgBox "Please Enter Name"

Exit Sub

Else

MultiPage1.Pages(0).Enabled = False
MultiPage1.Pages(1).Enabled = True
MultiPage1.Value = 1

End If


'Gender & Age are required to be selected to proceed to next Page:
Case 1

If optMale.Value = False And optFemale.Value = False Then

MsgBox "Please Select Gender"

Exit Sub

ElseIf lstAge.ListIndex = -1 Then

MsgBox "Please Select Age"

Else

MultiPage1.Pages(1).Enabled = False
MultiPage1.Pages(2).Enabled = True
MultiPage1.Value = 2

End If


'Marital Status & Residing Country are required to be selected to proceed to next Page:
Case 2

If optMarried.Value = False And optSingle.Value = False Then

MsgBox "Please Select Marital Status"

Exit Sub

ElseIf cmbCountry.Value = "" Then

MsgBox "Please Select or Enter Country"

Else

MultiPage1.Pages(2).Enabled = False
MultiPage1.Pages(3).Enabled = True
MultiPage1.Value = 3

End If

End Select


End Sub

 

 

 

Private Sub cmdClose_Click()
'set properties for CommandButton Close


Unload Me


End Sub 

 

 

------------------------------------------------------------------------------------------------------------------ 

 

TabStrip Control

 

A TabStrip control is used to view different contents in each Tab, for the same set of controls. A TabStrip is a collection of Tabs wherein each Tab contains a set of controls. By default, a TabStrip control has 2 Tabs. To Add Tabs, right-click on the Tab and select New Page (select Delete Page to delete a Tab).

 

 

Dynamically Add/Remove a Tab:

 

Using the Add Method: Syntax: Set t = TabStrip1.Tabs.Add(tabName, tabCaption, tabIndex). tabIndex (optional) is an integer which specifies the position for the Tab to be inserted, starting from 0 for the first position/Tab. tabName sets the Name for the Tab, tabCaption sets the Caption. Both are optional to specify.

 

Set t = TabStrip1.Tabs.Add("Tab4", "NewTab", 1) - this code adds a new Tab with name Tab4 and Caption NewTab, as the second Tab (viz. second position in the tab order).

 

TabStrip1.Tabs.Add "Tab3";    TabStrip1.Tabs(2).Caption = "Div 3"   -   the 2 codes add a new (third) Tab named Tab3 and set its Caption to Div 3.

 

 

To Remove a Tab - Syntax: TabStrip1.Tabs.Remove (tabCaption). Example: TabStrip1.Tabs.Remove ("Div 3") - this code removes Tab with Caption Div 3.

 

 

 

Difference between a MultiPage control and TabStrip control:

 

A MultiPage control is a container for controls, similar to a Frame. Each Page has a separate set of controls and selecting a Page (ie. making it visible) hides the other Pages of the MultiPage.

 

A TabStrip contains a consistent set of controls across all Tabs (within the TabStrip). The content of the controls changes when a different Tab is selected but the visibility or layout of the controls remain same.

 

 

 

Selecting a Tab:

 

To change or set the properties of a Tab at Run-time, we need to identify that Tab in the TabStrip control, which can be done in different ways. SelectedItem property of the TabStrip control indicates which Tab is selected. To select a Tab, set the Value property of the TabStrip control. Tab values start from 0, and the value of first Tab in a TabStrip control will be 0, value of second Tab will be 1, and so on. To access an individual Tab in a TabStrip control, the following methods can be used (refer below Example of vba codes):

 

1. Numeric Index (using the Tabs collection): Index 0 refers to the first page, Index 1 to the second page , and so on. Code to set Caption - TabStrip1.Tabs(Index).Caption

 

2. Item Method (using the Tabs collection): Item Index 0 refers to the first page, Item Index 1 to the second page , and so on. Code to set Caption - TabStrip1.Tabs.Item(ItemIndex).Caption

 

3. Tab Name: Code to set Caption - TabStrip1.Tabs("TabName").Caption or TabStrip1.Tabs.Item("TabName").Caption

 

4. Tab Object: Code to set Caption - TabStrip1.TabName.Caption

 

5. SelectedItem Property: Code to set Caption - TabStrip1.SelectedItem.Caption

 

 

 

Example 5: Change/set properties of each Tab of a TabStrip control, by using different methods of Tab Selection.

 

Private Sub UserForm_Initialize()
'change/set properties of each Tab of a TabStrip control, by using different methods of Tab Selection.


'using Numeric Index: sets Caption for the first Tab:
TabStrip1.Tabs(0).Caption = "Australia"


'using Item Method: sets Caption for the second Tab:
TabStrip1.Tabs.Item(1).Caption = "Canada"


'using Tab Name: sets Caption for the third Tab, whose name is Tab3:
TabStrip1.Tabs("Tab3").Caption = "France"
'alternate code: TabStrip1.Tabs.Item("Tab3").Caption = "France"


'using Tab Object: sets Caption for the fourth Tab, whose name is Tab4:
TabStrip1.Tab4.Caption = "Germany"


'using SelectedItem Property: sets Caption for the fifth Tab, which is the selected/active Tab:

'selects the fifth Tab
TabStrip1.Value = 4
'sets Caption for the selected Tab
TabStrip1.SelectedItem.Caption = "USA"

End Sub

 

 

Private Sub CommandButton1_Click()
'check Name and Caption of all Tabs in a TabStrip control.


Dim i As Integer


For i = 0 To TabStrip1.Tabs.Count - 1

MsgBox TabStrip1.Tabs(i).Name & " has a caption of " & TabStrip1.Tabs(i).Caption

Next i


End Sub

 

 

 

 

Example 6: How to work with a TabStrip control and Tabs - using a TabStrip and its Tabs to Load data from worksheet and to update worksheet range from Tab data. Refer Images 28a to 28c, and below vba codes:

 



 

Private Sub UserForm_Initialize()
'Set properties of Controls on initialization of UserForm.


'set Captions for each Tab:
With TabStrip1

.Tabs(0).Caption = "Div 1"
.Tabs(1).Caption = "Div 2"


'add new Tab named "Tab3" and set its Caption:
.Tabs.Add "Tab3"
.Tabs(2).Caption = "Div 3"

End With


'populate TextBoxes "Sales Target", "Actual Sales" & "Achieved (%)" for Division 1 from worksheet:
txtSalesTarget.Value = Sheet5.Range("B2").Value
txtActualSales.Value = Sheet5.Range("B3").Value
txtAchieved.Value = Round(Sheet5.Range("B4").Value * 100, 2) & " %"


'set first Tab as the selected Tab:
TabStrip1.Value = 0


'set properties of Label (viz. Caption, BackColor, Font & TextAlign) which appears on selection of the first Tab:
lblDivision.Caption = "Div 1: Sales Performance"
Me.lblDivision.BackColor = RGB(255, 0, 0)
Me.lblDivision.Font.Bold = True
Me.lblDivision.TextAlign = fmTextAlignCenter


'user not allowed to enter in TextBox of "Acheived (%)"
txtAchieved.Enabled = False


End Sub

 

 

 

Private Sub TabStrip1_Change()
'when a new Tab is selected, Label properties are set and TextBoxes are populated from worksheet:


Dim n As Integer


n = TabStrip1.SelectedItem.Index


Select Case n

Case 0

'on selection of first Tab, Caption of Label is set and its BackColor changes to Red:

lblDivision.Caption = "Div 1: Sales Performance"

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

 

'TextBoxes "Sales Target" & "Actual Sales" for Division 1 get populated from worksheet:

txtSalesTarget = Sheet5.Range("B2").Value

txtActualSales = Sheet5.Range("B3").Value

txtAchieved = Round(Sheet5.Range("B4").Value * 100, 2) & " %"

 

Case 1

'on selection of second Tab, Caption of Label is set and its BackColor changes to Green:

lblDivision.Caption = "Div 2: Sales Performance"

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

 

'TextBoxes "Sales Target" & "Actual Sales" for Division 2 get populated from worksheet:

txtSalesTarget = Sheet5.Range("C2").Value

txtActualSales = Sheet5.Range("C3").Value

txtAchieved = Round(Sheet5.Range("C4").Value * 100, 2) & " %"

 

Case 2

'on selection of third Tab, Caption of Label is set and its BackColor changes to Yellow:

lblDivision.Caption = "Div 3: Sales Performance"

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

 

'TextBoxes "Sales Target" & "Actual Sales" for Division 3 get populated from worksheet:

txtSalesTarget = Sheet5.Range("D2").Value

txtActualSales = Sheet5.Range("D3").Value

txtAchieved = Round(Sheet5.Range("D4").Value * 100, 2) & " %"

End Select


End Sub

 

 

 

Private Sub cmdClose_Click()
'clicking on "Close" button, unloads the UserForm


Unload Me


End Sub

 

 

 

Private Sub cmdClick_Click()
'Clicking the "Save" button will update worksheet with the values of each Tab.
'Note that worksheet Range("B4:D4") has a Percentage format of 2 decimals. Range("B4") has formula: =B3/B2; Range("C4") has formula: =C3/C2; and Range("D4") has formula: =D3/D2.


Dim n As Integer


n = TabStrip1.SelectedItem.Index


Select Case n

'update worksheet range per updated values in Text Boxes, for Division 1:

Case 0

If IsNumeric(txtSalesTarget.Value) And txtSalesTarget.Value > 0 And IsNumeric(txtActualSales.Value) Then

Sheet5.Range("B2").Value = txtSalesTarget.Value

Sheet5.Range("B3").Value = txtActualSales.Value

txtAchieved.Value = Round((txtActualSales.Value / txtSalesTarget.Value) * 100, 2) & " %"

Else

txtAchieved.Value = ""

End If

'update worksheet range per updated values in Text Boxes, for Division 2:
Case 1

If IsNumeric(txtSalesTarget.Value) And txtSalesTarget.Value > 0 And IsNumeric(txtActualSales.Value) Then

Sheet5.Range("C2").Value = txtSalesTarget.Value

Sheet5.Range("C3").Value = txtActualSales.Value

txtAchieved.Value = Round((txtActualSales.Value / txtSalesTarget.Value) * 100, 2) & " %"

Else

txtAchieved.Value = ""

End If

'update worksheet range per updated values in Text Boxes, for Division 3:
Case 2

If IsNumeric(txtSalesTarget.Value) And txtSalesTarget.Value > 0 And IsNumeric(txtActualSales.Value) Then

Sheet5.Range("D2").Value = txtSalesTarget.Value

Sheet5.Range("D3").Value = txtActualSales.Value

txtAchieved.Value = Round((txtActualSales.Value / txtSalesTarget.Value) * 100, 2) & " %"

Else

txtAchieved.Value = ""

End If

 End Select


End Sub

 

 

   

Bottom Ad

   
   
© 2014 GlobaliConnect.com. All rights reserved.