Dynamic ActiveX Controls

As previously stated, it is more difficult to program the ActiveX controls than the Form controls. At the same time, the ActiveX controls are more powerful, so it is a good idea to know how to program them.

You will see how to construct a combo box that behaves in a similar way to the drop-down in the previous example. Just to be different, use the BeforeRightClick event to trigger the appearance of a combo box in the D column of the SalesData worksheet, as follows:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _

Cancel As Boolean)

Dim ole As OLEObject

Dim ctl As MSForms.ComboBox

Dim lLine As Long

Dim oCodeModule As Object

'Turn screen updating off

Application.ScreenUpdating = False

'Determine if the combo box should be built

If Intersect(ActiveCell, Columns("D")) Is Nothing Then Exit Sub

On Error Resume Next

Set ole = Me.OLEObjects("Combo")

If Not ole Is Nothing Then

Cancel = True

Exit Sub

End If

On Error GoTo 0

'Add the combo box to the active cell

With ActiveCell

Set ole = Me.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _

Link:=False, DisplayAsIcon:=False, Left:=.Left, Top:

=.Top, _

Width:=.Width, Height:=


End With

ole.Name = "Combo"

Set ctl = ole.Object

ctl.Name = "Combo"

With ctl

.AddItem "Bananas"

.AddItem "Lychees"

.AddItem "Mangoes"

.AddItem "Rambutan"

End With

'Build the event procedure for the combo box click event

Set oCodeModule = ThisWorkbook.VBProject.VBComponents(Me.CodeName)


With oCodeModule

lLine = .CreateEventProc("Click", "Combo")

.ReplaceLine lLine + 1, " ProcessComboClick"

End With

Cancel = True

'Make sure the Excel window is active

Application.Visible = False

Application.Visible = True

End Sub

First, check to see that the event took place in the D column. Also, check to make sure that there is no existing combo box in the worksheet, which would mean that the user has created a combo box but has not yet selected an item from it. This did not matter in the previous example, where the combo boxes were independent even though they used the same OnAction code. Your ActiveX controls can't share the single Click event procedure you are going to create, so you need to ensure that you don't already have a control in the worksheet.

Use the name Combo for your ActiveX control. The quickest way to determine if there is already a control called Combo is to create an object variable referring to it. If this attempt fails, you know that the control does not exist. The error recovery code is used to ensure that the macro does not display an error message and stop running if the control does not exist. It would be friendlier to display an explanatory message before exiting the sub, but that is not the main point of this exercise. Setting Cancel to True suppresses the normal right-click menu from appearing.

If all is well, add a new combo box in the active cell. You need to know that an ActiveX object is not added directly onto a worksheet. It is contained in an OLEObject object, in the same way that a chart embedded in a worksheet is contained in a ChartObject object, as you saw in Chapter 8. The return value from the Add method of the OLEObjects collection is assigned to ole to make it easy to refer to the OLEObject object later. The Name property of the OleObject is changed to Combo to make it easy to identify later.

Next, create an object variable, ctl, referring to the ComboBox object contained in the OleObject, which is returned by the Object property of the OLEObject. The next line of code assigns the name Combo to the ComboBox object. This is not necessary in Excel 2007. When you assign a name to the OLEObject, it is also automatically assigned to the embedded object in these versions. This is not the case in Excel 97, where the name needs to be explicitly assigned.

Now you need to create the list of items to appear when the ComboBox is clicked. This can be done using the AddItem method, which needs to be executed for each item in the list.

Now create the Click event procedure code for the combo box. You can't create the event procedure in advance. It will cause compile errors if the ActiveX control it refers to does not exist. The methodology for creating event procedures programmatically is explained in detail in Chapter 26, so check that chapter for full details.

oCodeModule is assigned a reference to the class module behind the worksheet, and the CreateEventProc method of the code module is used to enter the first and last lines of the Combo_Click event procedure, with a blank line between. This method returns the line number of the first line of the procedure, which is assigned to lLine. The ReplaceLine method replaces the blank second line of the procedure with a call to a sub procedure named ProcessComboClick, which is listed in the next code snippet. The code for ProcessComboClick already exists in the worksheet's code module.

Set Cancel to True to ensure that the popup menu normally associated with a right-click in a cell does not appear.

Unfortunately, when you add code to a code module as done here, the code module is activated and the user could be left staring at a screen full of code. By setting the Excel application window's Visible property to False and then True, you ensure that the Excel window is active at the end of the procedure. There will still be some screen flicker, even though screen updating was suppressed at the start of the macro. It is possible to suppress this flicker by calls to the Windows API (discussed in Chapter 27).

The Click event procedure code that is created by the preceding code looks like the following:

Private Sub Combo_Click()

ProcessComboClick End Sub

When the user selects a value in the combo box, the Click event procedure executes and, in turn, executes ProcessComboClick. The code for ProcessComboClick, which is a permanent procedure in the worksheet's code module, contains the following:

Private Sub ProcessComboClick() Dim lLine As Long Dim lCount As Long Dim oCodeModule As Object

'Enter the chosen value With Me.OLEObjects("Combo")

.TopLeftCell.Value = .Object.Value .Delete End With

'Delete the combo box click event procedure Set oCodeModule = _


With oCodeModule lLine = .ProcStartLine("Combo_Click", 0) lCount = .ProcCountLines("Combo_Click", 0) .DeleteLines lLine, lCount End With

End Sub

The combo box is the object contained in the OLEObject named Combo. The preceding code enters the selected value from the combo box into the cell underneath the combo box, and then deletes the OLEObject and its contents.

The code then deletes the event procedure. oCodeModule is assigned a reference to the worksheet's code module. The ProcStartLine method returns the line number of the Combo_Click event procedure, including any blank lines or comment lines that precede it. The ProcCountLines method returns the number of lines in the procedure, including the lines that precede it. The Delete method removes all the lines in the procedure and the blank lines that precede it.

As you can see, dynamically coding an ActiveX control is quite complex. It is simpler to use a Forms toolbar control if you don't really need the extra power of an ActiveX control.

Was this article helpful?

0 0
The Accidental Blogging Millionaires

The Accidental Blogging Millionaires

Get Inspired By The Most Popular Bloggers Online! If You Want To Skyrocket Your Success With Business And Improve Your Overall Life You Need To Have A Look At The Accidental Blogging Millionaires! Business can be a fight, particularly when you’re trying to establish one online and like all fights, to succeed you must find the winning techniques and apply them.

Get My Free Ebook


Post a comment