Excel VBA command button group

In this post I am going to explain an nice and handy trick to be used with Excel VBA. If you want to create a matrix of command buttons that each button will return a certain action when clicked, then this will be so useful for you. With this trick you can create calendars, calculators, and even non-editable data tables.

The trick requires the following to be done on MultiPage control:

[1] Setting tab style as button

[2] Setting MultiRow option to "True": so if the MultiPage control width is not enough to contain all tabs in single row, extra tabs will be placed to additional rows.

[3] Setting tab orientation from top, so tabs will populate from top-left to bottom-right

[4] Using fixed tab width and height

[5] Finally, setting the width and height of the Multipage control to values that just show the tabs without showing pages. You can use the following formulas to calculate the required width and height.

MultiPage control width=(Tab width+2) x No. of columns

MultiPage control height=(Tab height+2) x No. of rows

Where the value [2] is the preceding padding before each tab.

All the previous actions can be done using the properties window of the control as shown below.

Now, if you want to add buttons to MultiPage control dynamically you can follow the following simple code:

' Populate buttons on user-form show up Private Sub UserForm_Initialize() MultiPage1.Pages.Clear     'Clear all existing pages in the Multipage control ' Add 49 tabs (buttons) For i = 0 To 48     '0 is the index of first page MultiPage1.Pages.Add MultiPage1.Pages(i).Caption = CStr(i) Next i End Sub ' Setting the action Private Sub MultiPage1_Change() NoteLabel.Caption = "The pressed button is " + CStr(MultiPage1.Value) End Sub

To make it more spicy, the following picture shows an application for this trick to create a calendar control. You can download this demo from this link

Tags:

Easy way to create group of command buttons

The easiest/simplest way/method to create group of command buttons

Excel VBA the easiest way to create popup menu

Excel VBA create and populate popup menu

Excel VBA popup menu in userform

Excel VBA popup menu in worksheet

Excel VBA TabStrip trick

Excel VBA MultiPage trick

Excel VBA calendar userform

Excel VBA calendar in worksheet