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
  • 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?
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Outlook Free & Paid Tools
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now