Text in Combo Boxes

[Bert Branchpipe]

Help anyone!

Is it possible to change the size of the text in Combo Boxes?........the default size is rather small.

Thanks in anticipation.

 

[Drmwvr7266]

Yes. It should be relatively easy. You need to open the VB editor and find your PROPERTIES box. Search under FONT. That's where all the necessary changes can be made.

 

[Bert Branchpipe]

OK, thanks for that, I've found the VB editor and the Properties Window but can't see anything to do with font size.

Any ideas?

 

[Ekim]

Bert,

There are two types of Combo boxes - the first comes from the Forms menu (View/Toolbars/Forms) and the second comes from the View/Toolbars/Toolbox.

You cannot change the font size for combo boxes from the Forms menu (this question has been posed many times on other Excel boards - nobody has ever offered a solution, VBA or otherwise). However, you can change the font size (including font color, font type e.g Arial and background color of the Combo box) for combo boxes from the View/Toolbars/Toolbox. You may wish to look at the Toolbox method.

If you get stuck, pose your questions in a new thread.

Regards,

Mike

 

[voodoo]

Is your combo box pasted onto an excel worksheet?

If so click on view then Toolbars then VisualBasic.

When the toolbar appears press Design Mode icon (shaped like a triangle and penicl). Then right click on the ComboBox and select properties. The properties window should open and your combo box should be the selected item.

Go down the list to Font. Click in the Font area and a small button with 3 dots should appear, click on this to open the Font Dialog Box. Use the Font dialog box as normal to select the size of the text.

This should now work for you. Close the properties box and re-click Design Mode to exit from it.

Try that and let us nkow if it works or if still having problems.

voodoo

 

[Bert Branchpipe]

Thanks folks

I see the problem, I have indeed created the Combo Boxes from the the Forms menu and now realise there properties cannot be changed.
I have found the Combo Boxes in Control Toolbox.

I have some more questions, please bear with me I had no idea I would have to start playing around with VB in order to design an Excel spreadsheet for my business!

How can you change the number of drop down lines, how do you enter text on each drop down line and will each line return an ascending numerical value when selected in the same way as the Combo Boxes in the Forms menu?

 

[voodoo]

You can use the addItem method to add items to the combo box

E.g.

cboXYZ.AddItem "Item One, 0
cboXYZ.AddItem "Item One, 1
cboXYZ.AddItem "Item One, 2
cboXYZ.AddItem "Item One, 3

etc

Try using an array and for next loop if you ahve a lot of entries.

The trailing number can be used as an index.

Set the item you want visible first like this

cboXYZ.ListIndex = 2

The numbre of visible rows in the drop down box depends on how many items are entered and Im not sure if this is contraollable anyway it would show a maximum of about 10 I think.

 

[Ivan F Moala]

On 2002-08-27 01:48, voodoo wrote:
You can use the addItem method to add items to the combo box

E.g.

cboXYZ.AddItem "Item One, 0
cboXYZ.AddItem "Item One, 1
cboXYZ.AddItem "Item One, 2
cboXYZ.AddItem "Item One, 3

etc

Try using an array and for next loop if you ahve a lot of entries.

The trailing number can be used as an index.

Set the item you want visible first like this

cboXYZ.ListIndex = 2

The numbre of visible rows in the drop down box depends on how many items are entered and Im not sure if this is contraollable anyway it would show a maximum of about 10 I think.

Hi Voodoo
Yes you can do it this way OR if you have
your data in the spreadsheet then link it to
the comboboxs [ListFillRange] eg A1:A10

The number of visible rows in the drop down
box is set by the [ListRows] property of the
Combobox: Default is 8.

 

[voodoo]

Just in case you need it here's a quick way to load data from a Excel spreadsheet into your dropdown box. Assuming your data is in column A starting at row 1.This assumes 40 items to be listed but you could code to check how many rows of data you need and set a variable for it.

Sub MacroFill()

Dim i as Integer
Dim s() as String

With ThisWorkbook.Worksheets("Sheet1")
For i = 1 to 40
s(i - 1) = .Cells(i, 1).Value
.ComboBox1.AddItem .Cells(i, 1).Value, i -1
Next
End With

End Sub

 

[voodoo]

Yes very good Ivan. I never knew there was such an option as ListFillRange, I think this must only be an option with Excel VBA as I cant find it in normal VB. What a convenient option.

for what its worth my post above forgot to declare the number of items in the array, this is the correct code

Sub MacroFill()

Dim i as Integer
Dim s(40) as String

With ThisWorkbook.Worksheets("Sheet1")
For i = 1 to 40
s(i - 1) = .Cells(i, 1).Value
.ComboBox1.AddItem .Cells(i, 1).Value, i -1
Next
End With

End Sub