View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Patrick Bateman Patrick Bateman is offline
external usenet poster
 
Posts: 41
Default Checkbox from control toolbar macro

Thanks peter that sorted it cheers!!
only thing is that it leaves the checkbox selected, how do i get the macro
to leave the box unticked?

"Peter T" wrote:

Sub test()
Dim c1&, c2&

c1 = RGB(51, 51, 153)
c2 = RGB(153, 204, 255)

With ActiveSheet.OLEObjects("checkbox1").Object ' change name
.Value = Not .Value
If .Value Then
.BackColor = c2
.ForeColor = c1
Else
.BackColor = c1
.ForeColor = c2
End If
.Font.Bold = True

End With
End Sub

Regards,
Peter T

"Patrick Bateman" wrote in
message ...
Hi,

i get the error "object required" when i tried it.
managed to find a way of setting the caption that works:

ActiveSheet.OLEObjects("checkbox2").Object.Caption = "Mechanical Complete"

but cant configure this to change the colour.

any ideas?

thankyou


"Rick Rothstein (MVP - VB)" wrote:

Yes, you just need to know the name of the control. For this example,

assume
the name is CheckBox1 (the default name Excel will give to the first

check
box)....

Sub Test()
With CheckBox1
.BackColor = vbRed
.ForeColor = vbWhite
.Caption = "HELLO"
End With
End Sub

Run the above macro and the check box will read HELLO in white letters

on a
red background. You can modify almost all of the controls (any control

you
place, not just a check box) using the structure above.

Rick


"Patrick Bateman" wrote in
message ...
Hi
is it possible to create a macro to change the colour and caption
properties
of a checkbox inserted friom the control toolbar?

thankyou for your help

Patrick