ActiveX controls and digital signatures in excel VBA

[Eoin McCarthy]

Hello,

I have a set of excel VBA applications that create and
delete controls on a worksheet.

Create code...

Dim cb As OLEObject
Dim targetCell As Range

Set targetCell = Cells(6, 2)
Set cb = ActiveWorkbook.ActiveSheet.OLEObjects.Add
(ClassType:="Forms.ComboBox.1", Left:=targetCell.Left + 2,
Top:=targetCell.Top + 5, Width:=targetCell.Width - 5,
Height:=targetCell.Height - 7)

Delete code....

Dim ws As Worksheet
ws.OLEObjects.Delete

The above code is an example - it is usually hooked into
open, close or double-click events.

To improve security and trust of our excel VBA apps I've
started to use digital signatures. However, the above code
causes the digital signature to be discarded after a user
activates it and tries to save his/her modifications to
the worksheet. The message "You have modified a signed
project. You do not have the right key to sign the
project. The signature will be discarded." is given.

However, a user can insert the corresponding excel form
control into a signed worksheet using the forms menu and
save it without affecting the digital signature. This
behaviour applies to both Office 2000 and Office XP.

The addition of a control via VBA offers the ability to
perform "lazy loading" of controls. For example, I have an
app that is essentially a task list spread over a number
of sheets. One of the columns is responsible for setting
the type of the task - a drop-down list is the obvious
choice for a control. But storing a control for every task
on every sheet is time and memory consuming. Instead, the
user is prompted to double-click on the column to generate
the dropdown list of options. It's a solution that has
worked well but now conflicts with a security policy.

I'm prepared to ditch this approach but would like to know
more about embedded controls - excel - digital signature
setup before I do so. I've not found much info in ms
sites.

Thanks,
Eoin

 

[Stan Scott]

Eoin,

There are two types of dropdowns in Excel. One is an OLEObject, but the
other isn't. This code creates a "regular" dropdown (from Excel Help):

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
lb.ControlFormat.ListFillRange = "A1:A10"
End With

For more information on ActiveX controls and Excel, there's a good article
at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcel9/html/xlactivexl.asp:
Using ActiveX Controls on a Microsoft Excel Worksheet
Stan Scott
New York City