Hi, I am trying to write a macro that includes putting 2 checkboxes onto a
sheet, then rewriting the text in them and changing the background colour.
so far all i have been able to do is set the position and size but cannot
set the text or colour.

any help would be much appreciated

thankyou

Patrick

I just created a macro by recording whilst inserting a checkbox, then
changing the text and background colour. Results below:

Sub InsertCheckbox()
ActiveSheet.CheckBoxes.Add(250.5, 42.75, 72, 72).Select
ActiveSheet.Shapes("Check Box 1").Select
Selection.Characters.Text = "New text"
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 53
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse
End Sub

You say you've already created the checkboxes, but you should be able to
extract the basis of what you need from this macro.

Hope this helps.

--
Ian
--
"Patrick Bateman" wrote in
message ...
Hi, I am trying to write a macro that includes putting 2 checkboxes onto a
sheet, then rewriting the text in them and changing the background colour.
so far all i have been able to do is set the position and size but cannot
set the text or colour.

any help would be much appreciated

thankyou

Patrick

i keep getting:

"object does not support this property or method"

"Ian" wrote:

I just created a macro by recording whilst inserting a checkbox, then
changing the text and background colour. Results below:

Sub InsertCheckbox()
ActiveSheet.CheckBoxes.Add(250.5, 42.75, 72, 72).Select
ActiveSheet.Shapes("Check Box 1").Select
Selection.Characters.Text = "New text"
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 53
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse
End Sub

You say you've already created the checkboxes, but you should be able to
extract the basis of what you need from this macro.

Hope this helps.

--
Ian
--
"Patrick Bateman" wrote in
message ...
Hi, I am trying to write a macro that includes putting 2 checkboxes onto a
sheet, then rewriting the text in them and changing the background colour.
so far all i have been able to do is set the position and size but cannot
set the text or colour.

any help would be much appreciated

thankyou

Patrick

I think Ian's code refers to a check box added from the Forms Toolbar. Try
something like this to change the text and color...

CheckBox1.Caption = "New Text Goes Here"
CheckBox1.BackColor = vbRed
CheckBox1.ForeColor = vbWhite

using your control's names, of course.

Rick


"Patrick Bateman" wrote in
message ...
i keep getting:

"object does not support this property or method"

"Ian" wrote:

I just created a macro by recording whilst inserting a checkbox, then
changing the text and background colour. Results below:

Sub InsertCheckbox()
ActiveSheet.CheckBoxes.Add(250.5, 42.75, 72, 72).Select
ActiveSheet.Shapes("Check Box 1").Select
Selection.Characters.Text = "New text"
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 53
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse
End Sub

You say you've already created the checkboxes, but you should be able to
extract the basis of what you need from this macro.

Hope this helps.

--
Ian
--
"Patrick Bateman" wrote in
message ...
Hi, I am trying to write a macro that includes putting 2 checkboxes
onto a
sheet, then rewriting the text in them and changing the background
colour.
so far all i have been able to do is set the position and size but
cannot
set the text or colour.

any help would be much appreciated

thankyou

Patrick

here is my code:


ActiveSheet.OLEObjects.Add(ClassType:="Forms.Check Box.1", Link:=False, _
DisplayAsIcon:=False, Left:=600.75, Top:=108.75, Width:=195.75,
Height _
:=51).Select
CheckBox1.Caption = "New Text Goes Here"
CheckBox1.BackColor = vbRed
CheckBox1.ForeColor = vbWhite

i get the error "object required"

have tried lots of different ways round it but nothing seems to work!
driving me crazy!!

"Rick Rothstein (MVP - VB)" wrote:

I think Ian's code refers to a check box added from the Forms Toolbar. Try
something like this to change the text and color...

CheckBox1.Caption = "New Text Goes Here"
CheckBox1.BackColor = vbRed
CheckBox1.ForeColor = vbWhite

using your control's names, of course.

Rick


"Patrick Bateman" wrote in
message ...
i keep getting:

"object does not support this property or method"

"Ian" wrote:

I just created a macro by recording whilst inserting a checkbox, then
changing the text and background colour. Results below:

Sub InsertCheckbox()
ActiveSheet.CheckBoxes.Add(250.5, 42.75, 72, 72).Select
ActiveSheet.Shapes("Check Box 1").Select
Selection.Characters.Text = "New text"
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 53
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoFalse
End Sub

You say you've already created the checkboxes, but you should be able to
extract the basis of what you need from this macro.

Hope this helps.

--
Ian
--
"Patrick Bateman" wrote in
message ...
Hi, I am trying to write a macro that includes putting 2 checkboxes
onto a
sheet, then rewriting the text in them and changing the background
colour.
so far all i have been able to do is set the position and size but
cannot
set the text or colour.

any help would be much appreciated

thankyou

Patrick