Check Box Macro
 

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

Check Box Macro
 

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

Check Box Macro
 

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

Check Box Macro
 

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

Check Box Macro
 

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