Insert checkbox and VBA code into Excel using Powershell

Question

Hi gurus,

I need help on how to include a checkbox into Excel and make a Msgbox appear saying "Hello" when it's ticked. That's not really what I need to do though, but I can find my own way from there.

This should be done using Powershell. Just to get started.

$missing = [System.Type]::missing
$excel = New-Object -Com Excel.Application

$wb = $excel.Workbooks.Add($missing)
$ws = $wb.Worksheets.Item(1)

########################################
# INSERT CHECKBOX HERE
# INSERT VBA CODE HERE
########################################

Thanks.

Answer 1

Adding the checkbox is pretty straight forward:

$oleObjects = $ws.OLEObjects($missing)
$checkbox = $oleObjects.Add("Forms.CheckBox.1",$missing,0,0,$missing,$missing,$missing,22.5,21,122.25,18.75)

Adding the VBA code on the fly, though, is driving me nuts. In C# interop it would be something like this:

Microsoft.Vbe.Interop.VBProject Project = Workbook.VBProject; 
Microsoft.Vbe.Interop.VBComponent Module = Project.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); 
Microsoft.Vbe.Interop.CodeModule Code = Module.CodeModule; 
Module.Name = Name; 
Code.AddFromString(VBACode); 

In PowerShell the VBProject.VBComponets returns null. My knowledge of PowerShell is pretty limited. I wonder if it could be a security problem. Recent versions of excel are very strict about adding VBA code programmatically.