Microsoft Access VBA Macro Programming

In the Custom UI Editor, use the Create Callbacks icon to copy across the three callbacks

forOnChange,GetItemCount, andGetItemLabel.

Paste these into your VBA module. You need to also define an array to hold the values for

the drop-down. Do this by putting the following statement in the declarations area of the

module:

Dim ComboArray( 3 )

This will set up an array with four elements beginning at index 0.

You then need to add in the following code for your callbacks:

Sub GetItemCount(control As IRibbonControl, ByRef returnedVal)

returnedVal = 4

End Sub

Sub GetItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)

ComboArray( 0 ) = "Option 1 "

ComboArray( 1 ) = "Option 2 "

ComboArray( 2 ) = "Option 3 "

ComboArray( 3 ) = "Option 4 "

returnedVal = ComboArray(index)

End Sub

Sub OnChange(control As IRibbonControl, text As String)

MsgBox text

End Sub

Looking at these in turn,GetItemCountreturns the number of items in the combo box

list, which is 4.

GetItemLabelis more complicated. This populates the four-element array that you set up

with option labels and then returns the array referenced by the index parameter (which is

based on the number of items declared inGetItemCount).

Finally,OnChangedisplays the selected text when the user chooses an item from the

combo box list.

Once you have put all this code into your module, you must then save the file and close

the database. The reason for this is thatGetItemCountandGetItemLabelare only called

when the file is loaded into Access. This means you can only declare your combo list at this

point and it cannot be changed dynamically during the operation of the spreadsheet. This is

something of a drawback.

When you load your file, click Home in the menu bar and click the combo box in your

custom control. You will see your four options listed (see Figure 11-8). Click one of those

options and the text will appear in a message box on the spreadsheet screen.

146 Microsoft Access 2010 VBA Macro Programming