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

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" <(E-Mail Removed)> wrote in
message news:627A6081-044A-48DD-B040-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in
> message news:627A6081-044A-48DD-B040-(E-Mail Removed)...
> > 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
>
>

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" <(E-Mail Removed)> wrote in
message news:FA6CC78A-3B48-4A6A-B1BC-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> > message news:627A6081-044A-48DD-B040-(E-Mail Removed)...
> > > 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
> >
> >

Do you have more than one check box on different sheets with the same name?
Try this modification to my previous code (I just added ActiveSheet in front
of the checkbox name and changed the referenced check box to CheckBox2 to
match the code you showed in your last posting... again, change the name as
required)...

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

The above code does work on my system. However, if you are still having
trouble with it, and if the code you posted is the only code that works for
you, then you can do it this way....

Sub Test()
With ActiveSheet.OLEObjects("CheckBox2").Object
.Caption = "HELLO"
.ForeColor = vbWhite
.BackColor = vbRed
End With
End Sub

Rick


"Patrick Bateman" <(E-Mail Removed)> wrote in
message news:FA6CC78A-3B48-4A6A-B1BC-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:627A6081-044A-48DD-B040-(E-Mail Removed)...
>> > 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
>>
>>

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" <(E-Mail Removed)> wrote in
> message news:FA6CC78A-3B48-4A6A-B1BC-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> > > message news:627A6081-044A-48DD-B040-(E-Mail Removed)...
> > > > 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
> > >
> > >
>
>
>

The demo toggled the ticked value and colours only for illustration, remove
it from your own code.

The code did nothing with respect to (de)Select. Ensure you exit design
mode.

Rick's example should also work fine.

Regards,
Peter T


"Patrick Bateman" <(E-Mail Removed)> wrote in
message news:A80AA744-79CA-41A2-A2C2-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
> > message news:FA6CC78A-3B48-4A6A-B1BC-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote
in
> > > > message news:627A6081-044A-48DD-B040-(E-Mail Removed)...
> > > > > 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
> > > >
> > > >
> >
> >
> >