Excel VBA

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

User Rating:  / 15
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:
MultiPage1.Value = 4
'selects the fifth Page
MultiPage1.SelectedItem.Caption = "USA"
'sets Caption for the selected page


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:
TabStrip1.Value = 4
'selects the fifth Tab
TabStrip1.SelectedItem.Caption = "USA"
'sets Caption for the selected Tab

 

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

 

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

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


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

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


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

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.