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 |
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 |
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 |
All times are GMT +1. The time now is 09:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com