ListBox Font & Font Size & Font Color Changes for Excel

Posted on 2002-05-15
Medium Priority
Last Modified: 2013-12-02
I have a spreadsheet, in which, I been asked to add some automations.

I would like to have a Listbox with some standard processing options on the main spreadsheet.  I can format font for command buttons with a right click and click Format Control, on the Font Tab.   But when I right click and click Format Control with a Listbox the Font Tab is missing ( same for ComboBox ).

I add items to the Listbox via VBA in the "Auto_Open" for the spreadsheet.   But the Font Size is way too small.  Plus I would like to change color to indicate conditions of operations.

I been through the helps, search this forum, some research in a search engine, look through the Object browser and can not find a work around for changing font parameters on a Listbox on top of a spreadsheet.

Thanks for your support & interest,  P1
Question by:P1
  • 4
  • 3
  • 2
  • +1

Author Comment

ID: 7011362
It's Excel 2K spreadsheet.

LVL 44

Expert Comment

ID: 7011380
Hi P1,

are you using the form controls from the control toolbox? those are easy to use and should have a property sheet

which pop up in design mode when you right click a control like a listbox or else

LVL 44

Expert Comment

ID: 7013023
Hi P1, is this listbox on the sheet or on a form?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 16

Expert Comment

ID: 7013117
Hi P1,
You have two sets of controls, both accessible from
Toolbars, and i guess you are using the Forms controls:
- Forms Toolbar: Form controls. Not very flexible (cannot
  change fonts,... but they are very easy to manipulate
  and can, in many cases, be used without any VBA.
  One more advantage: you can put them on graphs.
- Control Toolbox toolbar: They are more flexibles (can
  change fonts, formats,...), but they mainly need to be
  manipulated through vba code.

If you need to change fonts, formats,... you 'll have
to use the Control Toolbox controls.


Author Comment

ID: 7013672
I am using the ListBox control off of the Excel Forms Toolbar, which has been dropped onto a spreadsheet.

I'm using that because I can have it on top of the worksheet.

Regards,  P1
LVL 44

Accepted Solution

bruintje earned 300 total points
ID: 7013703
if it's not to confidential could you send me the sheet? as descriptions above already noted it should work


else when no one comes up with a solution i'll patch a sample together later
LVL 16

Expert Comment

ID: 7014343
Here is the directions to use ListBox from the
Control Toolbox:

Add Listbox on Sheet:
- From Excel (not vba editor) open the Control Toolbox
  toolbar (menu View>Toolbars>Control Toolbox)
- Add a listbox on the sheet
You notice it is given a default name, eg Listbox1, like
when added on a userform.

Change it main properties (...font...):
- Click on it to select it
- In the Control Toolbox toolbar, click the 'Properties'
  icon; the Property Page of the listbox pops up.
- There, you can set many properties of the listbox
  (like in the VBA Editor), ... change the font and any
  other property you need to change.

Add Items to Lisbox1
- Go in vba editor
- open the ThisWorkbook module and enter code:
  Private Sub Workbook_Open()
     With Sheet1.ListBox1
        .AddItem "Item#1"
        .AddItem "Item#2"
     End With
  End Sub
  This assumes the lisbox is named Lisbox1 and is on
  Sheet1 (code name of the sheet, not necessarily the
  name the user sees on the sheet tab in Excel)

Add Process Code:
- open the Sheet1 module
- Add the code:

   Private Sub ListBox1_Click()
      ProcessListBox ListBox1
   End Sub

   Private Sub ProcessListBox(lbx As MSForms.ListBox)
      'Process code here
      MsgBox "lisbox item selected"
   End Sub

   This assumes the Lisbox is named Listbox1 in Sheet1.

You're set.
Hope this helps.

Expert Comment

ID: 7015249
I'm jumping to a conclusion here. I have a number of Excel sheets, used for printing and display which have Combo boxes. I always find the font display on the combos and lists as unsatisfactory so... I make the control small, then trap the selected value, and place it in a formatted cell or range so that it is displayed how I want it. The code is straightforward e.g
Create a named range "HStyle", format it with any fnt and colour you choose. Then assuming a combo named CboHSTYLE

Private Sub CboHSTYLE_Change()
Range("HStyle").Value = CboHSTYLE.Value
End Sub

When the user click the combo control renamed CboHSTYLE and selects from list, the value is dropped into the previously formatted range "HStyle". You could equally use Cells(2, 4).Value    etc as in example below (ListIndex is the position of the selection in the list)

Hope this helps.

Geoff (macbone2)

Private Sub cboFillMktSrc_Change()
Dim ArrayRowNum As Long, RowNum As Long
ArrayRowNum = cboFillMktSrc.ListIndex
RowNum = ArrayRowNum + 2
Range("MSFill").Value = cboFillMktSrc.Value
Range("MSCFill").Value = Sheets("Sheet2").Cells(RowNum, 14)
End Sub

The range on which the selection list is based is in a range on "Sheet2" based on column M (13) but you can also fill other ranges using the row number

Author Comment

ID: 7042075
I sent the spreadsheet to Brian for analysis.

He was able to drop a "Regular" ListBox onto the spreadsheet from the "Control Toolbox" vs the "Forms" toolbox.   I work with some very old spreadsheets which the MS Forms 2.0 controls is all that was available.  
I was delighted to know that there were a way to use the "Regular" controls to which I was more familiar with from VB.

Thanks to everyone that participated in helping me!


Author Comment

ID: 7042095
Honorable mention goes to Sébastien.

I was involved with Bruintje at the time of his answer with problem.  


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question