Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically changing font size of combo box
Hi,
I have a spreadsheet that creates a number of combo boxes based on info in other spreadsheets and then fills them. Even so often, the font of these combo boxes is set to bold, but not all of them, and it appears almost random and therefore tricky to track down. So the simplest solution would be set the font.bold property of the control. However, I can't seem to reference the combo box once it's been created. I've used the OLEObjects.Add() contruct to add the combo boxes to the sheet, and the .Name property to name the control. The combo boxes have their LinkedCell property defined and that's how I reference the selected value for each combo box. The problem seems to be that using this construct adds it to the Shape collection and that doesn't seem to have a font property. Any help in simply setting the font to bold of the combo boxes will be appreciated. Thanks Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically changing font size of combo box
If you set a variable to the newly added combobox, your life will become a
little easier: Option Explicit Sub testme() Dim myCBX As OLEObject Dim wks As Worksheet Dim iCtr As Long Dim myRng As Range Set wks = ActiveSheet Set myRng = wks.Range("a1:d2") With myRng Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.ComboBox. 1", _ Link:=False, _ DisplayAsIcon:=False, Left:=.Left, _ Top:=.Top, Width:=.Width, Height:=.Height) End With With myCBX .Name = "Combobox_" & .TopLeftCell.Address(0, 0) .LinkedCell = wks.Range("a1").Address(external:=True) .Object.Font.Bold = True For iCtr = 1 To 10 .Object.AddItem "asdf" & iCtr Next iCtr End With End Sub Martin Walke wrote: Hi, I have a spreadsheet that creates a number of combo boxes based on info in other spreadsheets and then fills them. Even so often, the font of these combo boxes is set to bold, but not all of them, and it appears almost random and therefore tricky to track down. So the simplest solution would be set the font.bold property of the control. However, I can't seem to reference the combo box once it's been created. I've used the OLEObjects.Add() contruct to add the combo boxes to the sheet, and the .Name property to name the control. The combo boxes have their LinkedCell property defined and that's how I reference the selected value for each combo box. The problem seems to be that using this construct adds it to the Shape collection and that doesn't seem to have a font property. Any help in simply setting the font to bold of the combo boxes will be appreciated. Thanks Martin -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically changing font size of combo box
Thanks Dave. That's exactly what I do have - a reference to the control. It
was trying to figure out the properties/methods that would then allow me to access the font.bold property which was giving me grief, and obviously ..object is the beast! Thanks Martin "Dave Peterson" wrote in message ... If you set a variable to the newly added combobox, your life will become a little easier: Option Explicit Sub testme() Dim myCBX As OLEObject Dim wks As Worksheet Dim iCtr As Long Dim myRng As Range Set wks = ActiveSheet Set myRng = wks.Range("a1:d2") With myRng Set myCBX = .Parent.OLEObjects.Add(ClassType:="Forms.ComboBox. 1", _ Link:=False, _ DisplayAsIcon:=False, Left:=.Left, _ Top:=.Top, Width:=.Width, Height:=.Height) End With With myCBX .Name = "Combobox_" & .TopLeftCell.Address(0, 0) .LinkedCell = wks.Range("a1").Address(external:=True) .Object.Font.Bold = True For iCtr = 1 To 10 .Object.AddItem "asdf" & iCtr Next iCtr End With End Sub Martin Walke wrote: Hi, I have a spreadsheet that creates a number of combo boxes based on info in other spreadsheets and then fills them. Even so often, the font of these combo boxes is set to bold, but not all of them, and it appears almost random and therefore tricky to track down. So the simplest solution would be set the font.bold property of the control. However, I can't seem to reference the combo box once it's been created. I've used the OLEObjects.Add() contruct to add the combo boxes to the sheet, and the .Name property to name the control. The combo boxes have their LinkedCell property defined and that's how I reference the selected value for each combo box. The problem seems to be that using this construct adds it to the Shape collection and that doesn't seem to have a font property. Any help in simply setting the font to bold of the combo boxes will be appreciated. Thanks Martin -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
Size of font in Forms Combo Box | Excel Discussion (Misc queries) | |||
Dynamically changing the size of a list | Excel Discussion (Misc queries) | |||
How do I adjust the font size in a Combo Box? | Excel Discussion (Misc queries) | |||
how do i change the font size in a combo box? | Excel Worksheet Functions |