Dynamically changing font size of combo box

[Martin Walke]

Hi,

I have a spreadsheet that creates a number of combo boxes based on info in
other spreadsheets and then fills them. Even so often, the font of these
combo boxes is set to bold, but not all of them, and it appears almost
random and therefore tricky to track down. So the simplest solution would be
set the font.bold property of the control. However, I can't seem to
reference the combo box once it's been created.

I've used the OLEObjects.Add() contruct to add the combo boxes to the sheet,
and the .Name property to name the control. The combo boxes have their
LinkedCell property defined and that's how I reference the selected value
for each combo box. The problem seems to be that using this construct adds
it to the Shape collection and that doesn't seem to have a font property.

Any help in simply setting the font to bold of the combo boxes will be
appreciated.

Thanks
Martin

 

[Dave Peterson]

If you set a variable to the newly added combobox, your life will become a
little easier:

Option Explicit
Sub testme()

Dim myCBX As OLEObject
Dim wks As Worksheet
Dim iCtr As Long
Dim myRng As Range

Set wks = ActiveSheet

Set myRng = wks.Range("a1:d2")

With myRng
Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, Left:=.Left, _
Top:=.Top, Width:=.Width, Height:=.Height)
End With

With myCBX
.Name = "Combobox_" & .TopLeftCell.Address(0, 0)
.LinkedCell = wks.Range("a1").Address(external:=True)
.Object.Font.Bold = True
For iCtr = 1 To 10
.Object.AddItem "asdf" & iCtr
Next iCtr
End With

End Sub

 

[Martin Walke]

Thanks Dave. That's exactly what I do have - a reference to the control. It
was trying to figure out the properties/methods that would then allow me to
access the font.bold property which was giving me grief, and obviously
..object is the beast!

Thanks
Martin