Solved

ListBox Font & Font Size & Font Color Changes for Excel

Posted on 2002-05-15
10
2,211 Views
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
0
Comment
Question by:P1
[X]
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
10 Comments
 
LVL 4

Author Comment

by:P1
ID: 7011362
It's Excel 2K spreadsheet.

P1
0
 
LVL 44

Expert Comment

by:bruintje
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


:O)Bruintje
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7013023
Hi P1, is this listbox on the sheet or on a form?
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 16

Expert Comment

by:sebastienm
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.

Regards,
Sébastien
0
 
LVL 4

Author Comment

by:P1
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
0
 
LVL 44

Accepted Solution

by:
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

mulbum@worldonline.nl

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

Expert Comment

by:sebastienm
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.
Sébastien
0
 
LVL 2

Expert Comment

by:macbone2
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
0
 
LVL 4

Author Comment

by:P1
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!

P1
0
 
LVL 4

Author Comment

by:P1
ID: 7042095
Honorable mention goes to Sébastien.

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

P1
0

Featured Post

Industry Leaders: 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
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.

690 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