Filling a cell with an "X"

[Gadgetman]

Is there a way, other than using FORM check boxes, to fill a cell with
an "X" so that it takes up the entire cell and looks like a check box? I
thought I've seen this done somewhere, but I can't seem to figure out
how to do it. I don't want to just type an X in it and have it appear
like one lone character in a cell.

 

[JE McGimpsey]

one way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const nSTYLE As Long = xlContinuous
Const nWEIGHT As Long = xlThin
Const nCOLOR As Long = xlAutomatic
With Range("A1")
If Not Intersect(Target, .Cells) Is Nothing Then
If UCase(.Text) = "X" Then
.NumberFormat = ";;;"
With .Borders(xlDiagonalDown)
.LineStyle = nSTYLE
.Weight = nWEIGHT
.ColorIndex = nCOLOR
End With
With .Borders(xlDiagonalUp)
.LineStyle = nSTYLE
.Weight = nWEIGHT
.ColorIndex = nCOLOR
End With
Else
.NumberFormat = "General"
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End If
End If
End With
End Sub

 

[Gadgetman]

JE,
Where would I place your suggested text? Thanks.

 

[JE McGimpsey]

Put it in the worksheet code module (CTRL-click the worksheet tab and
choose View Code).

Take a look here:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Bob Greenblatt]

I've been fiddling with this problem and can offer an alternative. Use the
character palette to select the appropriate symbols. Then you can use a
simple if statement to test for an X and choose the appropriate symbol.
Unfortunately, there is no way to do this in the same cell without VBA code.
But, if you can live with the X in another column, then the following
formula will work:

=IF(A5="X","☒","â˜")
(I don't know how the Unicode characters will survive the news group
servers, but select the characters from the miscellaneous section of the
character palette.)

Or, you could use the following VBA code in the worksheet's code pane:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Value = "X" Then
Target = ChrW(9746)
Exit Sub
ElseIf AscW(Target) = 9744 Then
Exit Sub
ElseIf AscW(Target) = 9746 Then
Exit Sub
Else
Target = ChrW(9744)
End If
End Sub