Tab Strips and Multi Page Controls

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

I mentioned earlier that you can use frames to group related controls visually and help the user make sense of the form. However, there are two situations in which a frame falls down on the job.

The first situation is when you need the form to show multiple sets of the same (or similar) data. For example, suppose you have a form that shows values for sales and expense categories. You might want the form to be capable of showing separate data for various company divisions. One solution would be to create separate frames for each division and populate each frame with the same controls, but this is clearly inefficient. A second solution would be to use a list or a set of option buttons. This will work, but it might not be obvious to the user how he is supposed to display different sets of data, and these extra controls just serve to clutter the frame. A better solution is to create a tabbed form where each tab represents a different set of data.

The second situation is when you have a lot of controls. In this case, even the judicious use of frames won't be enough to keep your form from becoming difficult to navigate and understand. In situations where you have a large number of controls, you're better off creating a tabbed form that spreads the controls over several tabs.

In both of these situations, the tabbed form solution acts much like the tabbed dialog boxes you work with in Windows, Office, and other modern programs. To create tabs in your forms, VBA offers two controls: TabStrip and MultiPage.

The TabStrip Control

The TabStrip is an ideal way to give the user an intuitive method of displaying multiple sets of data. The basic idea behind the TabStrip control is that as the user navigates from tab to tab, the visible controls remain the same, and only the data displayed inside each control changes. The advantage here is that you need to create only a single set of controls on the form, and you use code to adjust the contents of these controls.

You create a TabStrip by clicking the TabStrip button in the Toolbox and then clicking and dragging the mouse until the strip is the size and shape you want. Here are a few points to keep in mind:

■ The best way to set up a TabStrip is to add it as the first control on the form and then add the other controls inside the TabStrip.

■ If you already have controls defined on the form, draw the TabStrip over the controls and then use the Send to Back command on the UserForm toolbar (or press Ctrl+K) to send the TabStrip to the bottom of the Z-order.

■ You can also display a series of buttons instead of tabs. To use this format, select the TabStrip and change the Style property to fmTabStyleButtons (or 1).

Figure 12.6 shows a form that contains a TabStrip control and an Excel worksheet that shows budget data for three different divisions. The goal here is to use the TabStrip to display budget data for each division as the user selects the tabs.

Figure 12.6

Using the form's TabStrip to display budget data from the three divisions in the Excel worksheet.

Figure 12.6 shows a form that contains a TabStrip control and an Excel worksheet that shows budget data for three different divisions. The goal here is to use the TabStrip to display budget data for each division as the user selects the tabs.

Change Vba Editor Appearance

The first order of business is to use code to change the tab captions, add a third tab, and enter the initial data. Listing 12.2 shows an Initialize event procedure that does just that.

Listing 12.2 An Initialize Event Procedure That Sets Up a TabStrip

Private Sub UserForm_Initialize()

1 Rename the existing tabs

With TabStrip1

.Tabs(0).Caption = "Division I" .Tabs(1).Caption = "Division II"

.Tabs.Add "Division III" End With

' Enter the intial data for Division I

With Worksheets("2007 Budget") txtSales = ,[B2] txtExpenses = .[B12] txtGrossProfit = .[B13] End With End Sub

The code first uses the Tabs collection to change the captions of the two existing tabs. The Tabs collection represents all the tabs in a TabStrip, and you refer to individual tabs by using an index number (where the first tab is 0, the second is 1, and so on). Then the Tabs collection's Add method is used to add a third tab titled Division III to the TabStrip. Finally, the three text boxes within the TabStrip (named txtSales, txtExpenses, and txtGrossProfit) are set to their respective values for Division I in the 2004 Budget worksheet.

Now you must set up a handler for when the user clicks a tab. This fires a Change event for the TabStrip, so you use this event handler to adjust the values of the text boxes, as shown in Listing 12.3.

Listing 12.3 A Change Event Procedure That Modifies the Controls Within a Tab Strip Whenever the User Selects a Different Tab

Private Sub TabStrip1_Change()

With Worksheets("2007 Budget") Select Case TabStrip1.Value Case 0

' Enter the data for Division I

txtSales = .[B2] txtExpenses = .[B12] txtGrossProfit = .[B13] Case 1

' Enter the data for Division II

txtSales = .[C2] txtExpenses = .[C12] txtGrossProfit = .[C13] Case 2

' Enter the data for Division III

txtSales = .[D2] txtExpenses = .[D12] txtGrossProfit = .[D13] End Select End With

End Sub

Here, a Select Case checks the Value property of the TabStrip (where the first tab has the value 0, the second tab has the value 1, and so on). Figure 12.7 shows the form in action. (See "Displaying the Form" later in this chapter to learn how to run a form.)

Figure 12.7

Clicking each tab displays the data for the appropriate division.

Figure 12.7

Clicking each tab displays the data for the appropriate division.

Excel Vba Refedit Multipage Userform

The MultiPage Control

The MultiPage control is similar to a TabStrip in that it displays a series of tabs along the top of the form. The major difference, however, is that each tab represents a separate form (called a page). Therefore, you use a MultiPage control whenever you want to display a different set of controls each time the user clicks a tab.

You add a MultiPage control to your form by clicking the MultiPage button in the Toolbox and then clicking and dragging the mouse until the control is the size and shape you want.

It's important to remember that each page in the control is a separate object (a Page object). So each time you select a page, the values that appear in the Properties window apply only to the selected page. For example, the Caption property determines the text that appears in the page's tab. Also, you set up a page by selecting it and then drawing controls inside the page. (If you have controls on the form already, you can put them inside a page by cutting them to the Clipboard, selecting the page, and pasting the controls.)

Working with a MultiPage control in code is very similar to working with a TabStrip:

■ The Pages collection represents all the pages inside a MultiPage control. You refer to individual pages by their index numbers.

■ Use the Pages.Add method to add more pages to the control.

■ When the user selects a different tab, the MultiPage control's Change event fires.

Was this article helpful?

0 0

Responses

  • taylor
    How to add values in tabstrip control in vba?
    9 years ago
  • Mattiesko
    How do you create a tabstrip vba?
    8 years ago

Post a comment