Excel Checkbox Alter Font by VBA

[Moonlight]

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

 

[merjet]

CheckBoxes from the Control Toolbox toolbar have a Font property. If
you are using CheckBoxes from the Forms toolbar, consider switching.

Hth,
Merjet

 

[Guest]

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.

 

[Moonlight]

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