ListBox Font & Font Size & Font Color Changes for Excel

Posted on 2002-05-15
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?
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!

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 100 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

726 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