Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
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?
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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