Example
The following example demonstrates how a combobox xan be added to a page (initCombobox.vb) and how events fired by the combobox can be caught (demoContainer.vb).
Note, the values filled into the combobox with addItem will be lost when the workbook is closed.
initCombobox.vb
option explicit
sub createCombobox() ' {
dim rng as range
set rng = democontainer.range(democontainer.cells(2,2), democontainer.cells(2, 4))
rng.entireRow.rowHeight = 17
dim obj as oleObject
set obj = democontainer.OLEObjects.add( _
classType := "Forms.Combobox.1" , _
link := false , _
displayAsIcon := false , _
left := rng.left , _
top := rng.top , _
width := rng.width , _
height := rng.height _
)
dim cb as msForms.comboBox
set cb = obj.object
with cb ' {
.name ="cb"
.columnCount = 3
.borderStyle = fmBorderStyleSingle
.columnWidths ="60;30;30"
.addItem : .list(0,0) = "Apple" : .list(0,1) = 17 : .list(0,2) = "abc"
.addItem : .list(1,0) = "Banana" : .list(1,1) = 12 : .list(1,2) = "def ghi"
.addItem : .list(2,0) = "Cherry" : .list(2,1) = 21 : .list(2,2) = "xyz"
end with ' }
obj.linkedCell = rng.resize(1,1).offset(3).address
end sub ' }
demoContainer.vb
option explicit
private sub cb_change()
dim id as long
id = me.cb.list(me.cb.listIndex, 1)
debug.print "value = " & me.cb.value & ", id = " & id
end sub
create.ps1
set-strictMode -version latest
$dir = split-path $myInvocation.myCommand.path
$wbFile = "$dir\combobox-text.xlsm"
$xls = get-msOfficeComObject excel
$wb = $xls.workbooks.add()
add-msVBEditorReference $xls '{0D452EE1-E08F-101A-852E-02608C4D0BB4}' 2 0 # Microsoft Forms 2.0
add-msVBEditorModule $xls "$dir/initCombobox.vb" 'initCombobox' 1
$null =
add-msVBEditorSheetWithModule $wb 'demoContainer' "$dir/demoContainer.vb"
$displayAlerts = $xls.displayAlerts
$xls.displayAlerts = $false
$wb.sheets("Sheet1").delete()
$xls.displayAlerts = $displayAlerts
$xls.run('createCombobox')
$wb.SaveAs($wbFile, 52) # 52 = xlsm