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