Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Checkbox Alter Font by VBA
Hi Everyone
First post for me - These Groups have been so useful I havn't needed to post before. Anyway I cannot get past this problem and I am running out of time. I am creating Checkboxes that go into cells in Excel and I need the caption to be in a non-proportional font so items in the caption will line up with each other. I cannot figure how to change the font. Here is some code. Dim myCell as Range Dim myCBX as Checkbox Set myCell = Worksheets(str_Wks).Range("A1").Offset(3, 5) With myCell .NumberFormat = ";;;" .Locked = False Set myCBX = .Parent.CheckBoxes.Add(Top:=.Top, Width:=130, _ Height:=.Height, Left:=.Left) End With With myCBX .Name = "cbx_" & myCell.Address(0, 0) .LinkedCell = myCell.Address(external:=True) .Caption = "Name of Cell" .Value = InputVal .Placement = xlMoveAndSize .OnAction = ThisWorkbook.Name & str_Action End With I have tried to add font name in the creation of the checkbox and in the cell address to no avail. I have also looked thru the groups to see if there has been a similar problem but no luck. - Any ideas??? Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Checkbox Alter Font by VBA
CheckBoxes from the Control Toolbox toolbar have a Font property. If
you are using CheckBoxes from the Forms toolbar, consider switching. Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Checkbox Alter Font by VBA
You are using a checkbox from the forms toolbar which does not support
changing the font. You would need to use a checkbox from the control toolbox toolbar to change the font: Sub AddCheckbox() Dim obox As OLEObject Dim cbox As MSForms.CheckBox Dim rng As Range Set rng = Range("B9") Set obox = ActiveSheet.OLEObjects.Add( _ ClassType:="Forms.CheckBox.1", _ Link:=False, _ DisplayAsIcon:=False, _ Left:=rng.Left, _ Top:=rng.Top, _ Width:=rng.Resize(1, 2).Width, _ Height:=rng.Height) Set cbox = obox.Object cbox.Font.Name = "Courier New" cbox.Caption = "My Checkbox" End Sub This type of control has a click event rather than an onaction property, so you would need to create that event or use an approach like John Walkenbach documents to have multiple controls use a single event code. \ http://www.j-walk.com/ss/excel/tips/tip44.htm event though the code is written for a commandbutton on a userform, the same approach can be used for ActiveX checkboxes on a worksheet. Also, I not you have your existing onaction code in the thisworkbook module. Probably better to move that code to a standard module and reserve the thisworkbook module for code related to workbook level events. -- Regards, Tom Ogilvy "Moonlight" wrote: Hi Everyone First post for me - These Groups have been so useful I havn't needed to post before. Anyway I cannot get past this problem and I am running out of time. I am creating Checkboxes that go into cells in Excel and I need the caption to be in a non-proportional font so items in the caption will line up with each other. I cannot figure how to change the font. Here is some code. Dim myCell as Range Dim myCBX as Checkbox Set myCell = Worksheets(str_Wks).Range("A1").Offset(3, 5) With myCell .NumberFormat = ";;;" .Locked = False Set myCBX = .Parent.CheckBoxes.Add(Top:=.Top, Width:=130, _ Height:=.Height, Left:=.Left) End With With myCBX .Name = "cbx_" & myCell.Address(0, 0) .LinkedCell = myCell.Address(external:=True) .Caption = "Name of Cell" .Value = InputVal .Placement = xlMoveAndSize .OnAction = ThisWorkbook.Name & str_Action End With I have tried to add font name in the creation of the checkbox and in the cell address to no avail. I have also looked thru the groups to see if there has been a similar problem but no luck. - Any ideas??? Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Checkbox Alter Font by VBA
Thanks for your replys
Yes Tom your right I didn't show the on click event code proceedures. They are in a different module and it is all working. It's just the appearance that I was concerned with. At the moment Im looking for a quick fix. However, to know I'm wasting my time looking for a font property is worth at least 2 hours - Thanks. I have to send the code out for apprasal first thing tomorrow morning. I think it will go as is (ie. working). If I get time later this week I will have a go at changing the text boxes over for the final version. Once again Thanks Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
font color with checkbox linked cel | Excel Discussion (Misc queries) | |||
Form CheckBox font size / style | Excel Discussion (Misc queries) | |||
Checkbox Font Size | Excel Discussion (Misc queries) | |||
use a font for checkbox | Excel Discussion (Misc queries) | |||
Clicking checkbox Changes the Label Font | Excel Programming |