How to increase font size in combo box Excel

I need to change the font size in an Excel combo box because the default font size is way too small. Is there a way to do this?

This is the code I use for my macro:

Option Explicit

Sub DropDown4_Change()
    Dim comboValue As String
    Dim Key1ColumnIndex As Integer
    Dim Key2ColumnIndex As Integer
    Dim Index As Integer
    Dim comboName As String
    Dim comboName2 As String
    Dim comboID As Integer

    'You can get the name by doing something like this in the immediate window:  "? Sheet1.Shapes(1).OLEFormat.Object.Name"

     For Index = 1 To ActiveSheet.Shapes.Count
        comboName = ActiveSheet.Shapes(Index).OLEFormat.Object.Name
        If InStr(comboName, "Drop") > 0 Then
            'MsgBox InStr(comboName, "Drop")
            comboName2 = comboName
            comboID = Index
        End If
     Next


    comboValue = ActiveSheet.Shapes(comboID).ControlFormat.List(ActiveSheet.Shapes(comboID).ControlFormat.ListIndex)

    Select Case comboValue

        Case "By Keyphrase"
            Key1ColumnIndex = 18
            Key2ColumnIndex = 19
        Case "By Region"
            Key1ColumnIndex = 19
            Key2ColumnIndex = 18
        Case "Default"
            Key1ColumnIndex = 1
            Key2ColumnIndex = 1
    End Select


   Range("DataValues").sort Key1:=Range("DataValues").Cells(1, Key1ColumnIndex), _
                            Order1:=xlAscending, Header:=xlNo, DataOption1:=xlSortNormal, _
                            Key2:=Range("DataValues").Cells(1, Key2ColumnIndex), order2:=xlAscending
End Sub

Thank you.

Deduplicator

44k6 gold badges63 silver badges114 bronze badges

asked Oct 3, 2011 at 14:05

It can't be done

There's no formatting the font size of a forms dropdown - even programatically. If it's an absolute requirement, you'll have to switch this to an activeX control.

answered Oct 3, 2011 at 14:37

AlainAlain

26.3k19 gold badges109 silver badges180 bronze badges

As said by @Alain you simply can't in a clean way, or you have to use an activeX control.

But here there's also a workaround. With programming, you can temporarily zoom the worksheet, to make the data validation font size appear larger.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
  If Target.Address  = "$A$2" Then 
    ActiveWindow.Zoom = 120 
  Else 
    ActiveWindow.Zoom = 100 
  End If 
End Sub 

Credits and more details: https://www.contextures.com/xldataval08.html#zoommacro

answered Mar 24, 2021 at 13:22

AndrewAndrew

911 silver badge11 bronze badges

Created: Jun 17, 2020 07:44 PM  |  Last Modified: Jun 17, 2020 07:49 PM (1844 views)  |  Posted in reply to message from David_Burnham 06-17-2020

or ... 

replace the combo box with a text box.  Use the text box to show the name of the selected parameter (using a small font).  Next to it, have a "select" button.  Use the button script to launch a new window containing the combo box or list box with the parameter names.  Since it is a separate window with no other content you should be able to display the full length names without any problem); make this window modal and update the text box when the window is closed.

Here is some crude code to illustrate the idea:

names default to here(1);

NewWindow("Test",
	BorderBox(top(20),bottom(20),left(20),right(20),
		VListBox(
			TextBox("Selected Parameter",<<setFontStyle("Bold")),
			HListBox(
				here:tb = TextEditBox("parameter name 1",<<enable(0),<<setWidth(130)),
				SpacerBox(size(6,0)),
				ButtonBox("select",
					NewWindow("Select",<<modal,
						<<onCLose(
							here:tb<<setText(cb<<getSelected)
						),
						BorderBox(top(20),bottom(20),left(20),right(20),
							cb = ComboBox({"parameter name 1","parameter name 2"})
						)
					),
					underlinestyle(1)
				)
			)
		)
	)
)

-Dave

How do I format text in a combobox in Excel?

How do I change the font size in a drop

How do I increase font size in Formula Bar?