The document I am creating will be rejected if boxes within it are marked
with a tick. The checkbox appears to only activate a tick - can I change
this to a cross?

There's no way that I know of to do this. A suggestion, if it's that
essential, is to make your own using a rectangle from the Drawing toolbar and
use an underlying cell for the "X" mark. You need to set the Visible property
of the rectangle's Fill and Line to False. You also need to assign a macro to
the rectangle that toggles the cell between "X" and "". I think if you format
the cell's font name to Marlett and toggle an "r" instead it will look better
(an "r" in Marlett looks like an "X").

If you need it to have the 3D appearance, I have code contained in a utility
of mine that can replicate the Sunken 3D special effect which you can apply
to the box. You should be able to make it look the same as the Forms
checkbox. It will be a pain to dissect it out of the utility, so I don't want
to take this step unless it's essential.

Example code that will create what I'm talking about follows. Run it with a
blank sheet active. As usual, the code is "quick and dirty".

Regards,
Greg

Sub MakeXBox()
Dim shp As Shape
Dim c As Range
Dim L As Single, T As Single
Dim W As Single, H As Single

Set c = ActiveSheet.Range("C5")
With c
.Offset(-1, -1).Resize(3, 3).Interior.ColorIndex = 15
.Interior.ColorIndex = xlNone
.BorderAround xlContinuous, xlThin
.Font.Name = "Marlett"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 9
.ColumnWidth = 1.5
.Value = "r"
.Offset(-1).RowHeight = 5
.Offset(0, -1).ColumnWidth = 0.5
.Offset(1).RowHeight = 5
.Locked = False
With .Offset(, 1)
.ColumnWidth = 15
.Value = "Test 1234"
.VerticalAlignment = xlCenter
End With
L = .Left - 4: T = .Top - 4
W = 100: H = .Height + 10
End With
With ActiveSheet
.Rows(c.Row).RowHeight = .Columns(c.Column).Width
Set shp = .Shapes.AddShape(1, L, T, W, H)
With shp
.Fill.Visible = False
.Line.Visible = False
.OnAction = "TogXBox"
End With
.Protect
End With
ActiveWindow.DisplayGridlines = False
End Sub
Sub TogXBox()
With ActiveSheet.Shapes(Application.Caller).TopLeftCell (2, 2)
If .Value = "r" Then .Value = "" Else .Value = "r"
End With
End Sub

Hi Greg

Thanks for your assistance. Unfortunately I only know very basic excel -
when I follow your instructions I come up with an error message "compile
Error: Expected end Sub" - can you assist????

Thanks
Denise


"Greg Wilson" wrote:

There's no way that I know of to do this. A suggestion, if it's that
essential, is to make your own using a rectangle from the Drawing toolbar and
use an underlying cell for the "X" mark. You need to set the Visible property
of the rectangle's Fill and Line to False. You also need to assign a macro to
the rectangle that toggles the cell between "X" and "". I think if you format
the cell's font name to Marlett and toggle an "r" instead it will look better
(an "r" in Marlett looks like an "X").

If you need it to have the 3D appearance, I have code contained in a utility
of mine that can replicate the Sunken 3D special effect which you can apply
to the box. You should be able to make it look the same as the Forms
checkbox. It will be a pain to dissect it out of the utility, so I don't want
to take this step unless it's essential.

Example code that will create what I'm talking about follows. Run it with a
blank sheet active. As usual, the code is "quick and dirty".

Regards,
Greg

Sub MakeXBox()
Dim shp As Shape
Dim c As Range
Dim L As Single, T As Single
Dim W As Single, H As Single

Set c = ActiveSheet.Range("C5")
With c
.Offset(-1, -1).Resize(3, 3).Interior.ColorIndex = 15
.Interior.ColorIndex = xlNone
.BorderAround xlContinuous, xlThin
.Font.Name = "Marlett"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 9
.ColumnWidth = 1.5
.Value = "r"
.Offset(-1).RowHeight = 5
.Offset(0, -1).ColumnWidth = 0.5
.Offset(1).RowHeight = 5
.Locked = False
With .Offset(, 1)
.ColumnWidth = 15
.Value = "Test 1234"
.VerticalAlignment = xlCenter
End With
L = .Left - 4: T = .Top - 4
W = 100: H = .Height + 10
End With
With ActiveSheet
.Rows(c.Row).RowHeight = .Columns(c.Column).Width
Set shp = .Shapes.AddShape(1, L, T, W, H)
With shp
.Fill.Visible = False
.Line.Visible = False
.OnAction = "TogXBox"
End With
.Protect
End With
ActiveWindow.DisplayGridlines = False
End Sub
Sub TogXBox()
With ActiveSheet.Shapes(Application.Caller).TopLeftCell (2, 2)
If .Value = "r" Then .Value = "" Else .Value = "r"
End With
End Sub

Hi Denise,
It would appear that when you copied Greg's code above
you missed the last line:
-- End Sub <--
Dave
Den Wrote:
Hi Greg

Thanks for your assistance. Unfortunately I only know very basic excel
-
when I follow your instructions I come up with an error message
"compile
Error: Expected end Sub" - can you assist????

Thanks
Denise


"Greg Wilson" wrote:

There's no way that I know of to do this. A suggestion, if it's that
essential, is to make your own using a rectangle from the Drawing
toolbar and
use an underlying cell for the "X" mark. You need to set the Visible
property
of the rectangle's Fill and Line to False. You also need to assign a
macro to
the rectangle that toggles the cell between "X" and "". I think if
you format
the cell's font name to Marlett and toggle an "r" instead it will
look better
(an "r" in Marlett looks like an "X").

If you need it to have the 3D appearance, I have code contained in a
utility
of mine that can replicate the Sunken 3D special effect which you can
apply
to the box. You should be able to make it look the same as the Forms
checkbox. It will be a pain to dissect it out of the utility, so I
don't want
to take this step unless it's essential.

Example code that will create what I'm talking about follows. Run it
with a
blank sheet active. As usual, the code is "quick and dirty".

Regards,
Greg

Sub MakeXBox()
Dim shp As Shape
Dim c As Range
Dim L As Single, T As Single
Dim W As Single, H As Single

Set c = ActiveSheet.Range("C5")
With c
.Offset(-1, -1).Resize(3, 3).Interior.ColorIndex = 15
.Interior.ColorIndex = xlNone
.BorderAround xlContinuous, xlThin
.Font.Name = "Marlett"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 9
.ColumnWidth = 1.5
.Value = "r"
.Offset(-1).RowHeight = 5
.Offset(0, -1).ColumnWidth = 0.5
.Offset(1).RowHeight = 5
.Locked = False
With .Offset(, 1)
.ColumnWidth = 15
.Value = "Test 1234"
.VerticalAlignment = xlCenter
End With
L = .Left - 4: T = .Top - 4
W = 100: H = .Height + 10
End With
With ActiveSheet
.Rows(c.Row).RowHeight = .Columns(c.Column).Width
Set shp = .Shapes.AddShape(1, L, T, W, H)
With shp
.Fill.Visible = False
.Line.Visible = False
.OnAction = "TogXBox"
End With
.Protect
End With
ActiveWindow.DisplayGridlines = False
End Sub
Sub TogXBox()
With ActiveSheet.Shapes(Application.Caller).TopLeftCell (2, 2)
If .Value = "r" Then .Value = "" Else .Value = "r"
End With
End Sub



--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=494056

Hi

No - I have double checked it and the last line was copied in

"Desert Piranha" wrote:


Hi Denise,
It would appear that when you copied Greg's code above
you missed the last line:
-- End Sub <--
Dave
Den Wrote:
Hi Greg

Thanks for your assistance. Unfortunately I only know very basic excel
-
when I follow your instructions I come up with an error message
"compile
Error: Expected end Sub" - can you assist????

Thanks
Denise


"Greg Wilson" wrote:

There's no way that I know of to do this. A suggestion, if it's that
essential, is to make your own using a rectangle from the Drawing
toolbar and
use an underlying cell for the "X" mark. You need to set the Visible
property
of the rectangle's Fill and Line to False. You also need to assign a
macro to
the rectangle that toggles the cell between "X" and "". I think if
you format
the cell's font name to Marlett and toggle an "r" instead it will
look better
(an "r" in Marlett looks like an "X").

If you need it to have the 3D appearance, I have code contained in a
utility
of mine that can replicate the Sunken 3D special effect which you can
apply
to the box. You should be able to make it look the same as the Forms
checkbox. It will be a pain to dissect it out of the utility, so I
don't want
to take this step unless it's essential.

Example code that will create what I'm talking about follows. Run it
with a
blank sheet active. As usual, the code is "quick and dirty".

Regards,
Greg

Sub MakeXBox()
Dim shp As Shape
Dim c As Range
Dim L As Single, T As Single
Dim W As Single, H As Single

Set c = ActiveSheet.Range("C5")
With c
.Offset(-1, -1).Resize(3, 3).Interior.ColorIndex = 15
.Interior.ColorIndex = xlNone
.BorderAround xlContinuous, xlThin
.Font.Name = "Marlett"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 9
.ColumnWidth = 1.5
.Value = "r"
.Offset(-1).RowHeight = 5
.Offset(0, -1).ColumnWidth = 0.5
.Offset(1).RowHeight = 5
.Locked = False
With .Offset(, 1)
.ColumnWidth = 15
.Value = "Test 1234"
.VerticalAlignment = xlCenter
End With
L = .Left - 4: T = .Top - 4
W = 100: H = .Height + 10
End With
With ActiveSheet
.Rows(c.Row).RowHeight = .Columns(c.Column).Width
Set shp = .Shapes.AddShape(1, L, T, W, H)
With shp
.Fill.Visible = False
.Line.Visible = False
.OnAction = "TogXBox"
End With
.Protect
End With
ActiveWindow.DisplayGridlines = False
End Sub
Sub TogXBox()
With ActiveSheet.Shapes(Application.Caller).TopLeftCell (2, 2)
If .Value = "r" Then .Value = "" Else .Value = "r"
End With
End Sub



--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=494056

Hi Denise,

Well, I don't know then, I copied the code of Greg's and it works
here.
Someone with more smarts than me will chime in.

Dave
Den Wrote:
Hi

No - I have double checked it and the last line was copied in

"Desert Piranha" wrote:


Hi Denise,
It would appear that when you copied Greg's code above
you missed the last line:
-- End Sub <--
Dave
Den Wrote:
Hi Greg

Thanks for your assistance. Unfortunately I only know very basic
excel
-
when I follow your instructions I come up with an error message
"compile
Error: Expected end Sub" - can you assist????

Thanks
Denise


"Greg Wilson" wrote:

There's no way that I know of to do this. A suggestion, if it's
that
essential, is to make your own using a rectangle from the
Drawing
toolbar and
use an underlying cell for the "X" mark. You need to set the
Visible
property
of the rectangle's Fill and Line to False. You also need to
assign a
macro to
the rectangle that toggles the cell between "X" and "". I think
if
you format
the cell's font name to Marlett and toggle an "r" instead it
will
look better
(an "r" in Marlett looks like an "X").

If you need it to have the 3D appearance, I have code contained
in a
utility
of mine that can replicate the Sunken 3D special effect which you
can
apply
to the box. You should be able to make it look the same as the
Forms
checkbox. It will be a pain to dissect it out of the utility, so
I
don't want
to take this step unless it's essential.

Example code that will create what I'm talking about follows. Run
it
with a
blank sheet active. As usual, the code is "quick and dirty".

Regards,
Greg

Sub MakeXBox()
Dim shp As Shape
Dim c As Range
Dim L As Single, T As Single
Dim W As Single, H As Single

Set c = ActiveSheet.Range("C5")
With c
.Offset(-1, -1).Resize(3, 3).Interior.ColorIndex = 15
.Interior.ColorIndex = xlNone
.BorderAround xlContinuous, xlThin
.Font.Name = "Marlett"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Size = 9
.ColumnWidth = 1.5
.Value = "r"
.Offset(-1).RowHeight = 5
.Offset(0, -1).ColumnWidth = 0.5
.Offset(1).RowHeight = 5
.Locked = False
With .Offset(, 1)
.ColumnWidth = 15
.Value = "Test 1234"
.VerticalAlignment = xlCenter
End With
L = .Left - 4: T = .Top - 4
W = 100: H = .Height + 10
End With
With ActiveSheet
.Rows(c.Row).RowHeight = .Columns(c.Column).Width
Set shp = .Shapes.AddShape(1, L, T, W, H)
With shp
.Fill.Visible = False
.Line.Visible = False
.OnAction = "TogXBox"
End With
.Protect
End With
ActiveWindow.DisplayGridlines = False
End Sub
Sub TogXBox()
With ActiveSheet.Shapes(Application.Caller).TopLeftCell (2, 2)
If .Value = "r" Then .Value = "" Else .Value = "r"
End With
End Sub



--
Desert Piranha



------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread:
http://www.excelforum.com/showthread...hreadid=494056




--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=494056