change font size of dropdown list

[xuefang]

I've created a combo box in excel with the macro recorder. This is the code that came back:

Sub test1()
ActiveSheet.DropDowns.Add(1305, 52.5, 242.25, 39).Select
With Selection
.ListFillRange = "$AW$4:$AW$18"
.LinkedCell = "$AX$5"
.DropDownLines = 5
.Display3DShading = False
End With
End Sub

This always results in a default font size of 8 for my list. Can anyone tell me how to modify the code above to change the font size to 14? And how to modify the color of the list.Thanks!

 

[SkipVought]

Hi,

MS Forms controls are elementary controls and do not have those properties to change. They can be used with native sheet functionality.

Check out the Control Toolbox controls. HOWEVER, they require VBA Code to use.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[xuefang]

Thanks you very much. Now I know I can't control the font size of the combo box created by Forms control. But I really like this combo box, the code is simple and the box is clear and neat. Not like the combo box created by Control Toolbox, which is very complex.

 

[xuefang]

Thank you very much.I follow your advice and I try to use Toolbox control to create the combo box.Now I can set font size and color of the dropdown list. It works fine.But I got another problem. I don't know how to set an onAction when user choose an item in the combo box.
There are two parts of codes below.

'code 1
Sub addCombox()
ThisWorkbook.Names.Add Name:="dynaLine", _
    RefersTo:="=OFFSET(elements!$B$1:$B$200,0,0,COUNTA(elements!$B:$B),1)", Visible:=True
    Sheets("Contents ").Select
    ActiveSheet.DropDowns.Add(Range("A1").Width, Range("A1:A2").height, _
    Range("B1:C1").Width, Cells(3, 1).height + Cells(4, 1).height).Select
    With Selection
        .ListFillRange = "dynaLine"
        .LinkedCell = "elements!$D$1"
        .DropDownLines = 30
        .Display3DShading = True
    End With
    Selection.OnAction = "comAct"
End Sub

'*******************************************************
'code 2
Sub amyComboBox()

    Dim x As MSForms.ComboBox, z As OLEObject
    Const myCBName As String = "chooseItem"
   
    On Error Resume Next
    ActiveSheet.OLEObjects(myCBName).Delete
   
    On Error GoTo 0
    Set z = ActiveSheet.OLEObjects.Add(ClassType:="forms.combobox.1", Link:=False, _
             DisplayAsIcon:=False, Left:=235.5, Top:=27, Width:=139.5, height:= _
        20.25)
    z.Name = myCBName
    Set x = z.Object
   
    With x
        .columnCount = 1
      ' .ColumnWidths = "1 in; 1 in; 1 in;"  'not needed - only 1 column
        .Font.Name = "Arial"
        .Font.Size = 10
        .BackStyle = 0
        .BorderStyle = 1
        .DropButtonStyle = 1
        .TextAlign = 1  ' 2 = Center 3 = Right
        .SpecialEffect = 0
        .BackColor = &HC0FFFF
        .ListRows = 30
        .ForeColor = &HFF00FF
        
    End With
   
    With z
        .LinkedCell = "elements!$D$3"
        .ListFillRange = "dynaLine"
        
    End With
end sub
[/code ]

code 1 is using Forms control to create combo box I used before.
code 2 is using Toolbox control. But I did not finish this part.Becuase It doesn't have the function as in code 1: "Selection.OnAction = "comAct" in this section.What I want to do is: when user choose a item in the combo box,A macro called "comAct" will execute.

Any advice are greatly appreciated. Thanks.

 

[SkipVought]

Please post VBA code related questions in Forum707.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]