Size of Font in Combo Box

[Pierre]

For some screen the font is too small to be legible.
How can I change the size of the font in a list box? I see
nothing in the Code or propertie related to Font Size.
Thanks,
Pierre

 

[Debra Dalgleish]

If the dropdown is a combo box from the Forms toolbar, the font size
can't be changed.

You could use the combo box from the Control Toolbox, for which you can
change the font.

1. Right-click on the combo box and choose Properties.
2. Click in the font cell, and click the ... button.
3. Choose a font and size.

 

[Pierre]

Thanks Debra for the quick reply.
Is there a way I can switch/copy/whatever the box from
type A to type B, or do I need to redo them all?
Thanks again
Pierre

 

[Debra Dalgleish]

Pierre, there's no way to convert one type to the other. However, if
there are many to change, you could use a macro to delete the existing
comboboxes, and add the new ones. For example, the following macro adds
comboboxes in column B, using a named range (MonthList) as the
ListFillRange.

Sub AddCombox()

Dim oleObj As OLEObject
Dim c As Range
Dim i As Integer

With ActiveSheet
'delete the comboboxes from the Forms toolbar
.DropDowns.Delete
'add comboboxes from Control toolbox
For i = 1 To 10
Set c = .Range("B" & i + 1)
With c
Set oleObj = .Parent.OLEObjects _
.Add(ClassType:="Forms.comboBox.1", _
Left:=.Left, Top:=.Top, Width:=.Width, _
Height:=.Height)
End With

With oleObj
.Name = "MyCombo" & i
.ListFillRange = "MonthList"
End With
Next i
End With

End Sub

 

[Dave Peterson]

I like Debra's approach, but I think you could steal more info from the dropdown
before you delete it.

Option Explicit
Sub ReplaceDropDownsWithComboboxes()

Dim oleObj As OLEObject
Dim myDD As DropDown
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
For Each myDD In wks.DropDowns
With myDD
Set oleObj = _
.Parent.OLEObjects.Add(ClassType:="Forms.comboBox.1", _
Left:=.Left, Top:=.Top, Width:=.Width, _
Height:=.Height)
oleObj.ListFillRange = .ListFillRange
oleObj.LinkedCell = .LinkedCell
If .ListIndex = 0 Then
'do nothing
Else
oleObj.Object.Value = .List(.ListIndex)
End If
.Name = "tmp_" & .Name
oleObj.Name = Mid(.Name, 5)
.Delete 'get rid of the old dropdown
End With
Next myDD
End With

End Sub

I stopped stealing with the listfillrange, the linked cell and the name. (and
the location, too!)

 

[Debra Dalgleish]

Nice! I think I'll steal it.

I like Debra's approach, but I think you could steal more info from the dropdown
before you delete it.

Option Explicit
Sub ReplaceDropDownsWithComboboxes()

Dim oleObj As OLEObject
Dim myDD As DropDown
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
For Each myDD In wks.DropDowns
With myDD
Set oleObj = _
.Parent.OLEObjects.Add(ClassType:="Forms.comboBox.1", _
Left:=.Left, Top:=.Top, Width:=.Width, _
Height:=.Height)
oleObj.ListFillRange = .ListFillRange
oleObj.LinkedCell = .LinkedCell
If .ListIndex = 0 Then
'do nothing
Else
oleObj.Object.Value = .List(.ListIndex)
End If
.Name = "tmp_" & .Name
oleObj.Name = Mid(.Name, 5)
.Delete 'get rid of the old dropdown
End With
Next myDD
End With

End Sub

I stopped stealing with the listfillrange, the linked cell and the name. (and
the location, too!)

 

[Dave Peterson]

Bucky would. Satchell wouldn't!
<vbg>

Nice! I think I'll steal it.

 

[Debra Dalgleish]

Fuzzy logic?

Since this thread originally asked about font size, I added a couple of
lines to set the font and font size:

oleObj.LinkedCell = .LinkedCell
oleObj.Object.Font.Size = 11
oleObj.Object.Font.Name = "Times New Roman"

 

[Dave Peterson]

Oh sure. Give the answer that Pierre really cares about!