HOW DO I GET AN X IN A CELL IN EXCEL TO COME & GO WHEN CLICKED

C

carbetboy

i am trying to put a tick box into a spread sheet and have failed miserably,
any info greatly appreciated
 
T

T. Valko

One way...

Assume you want the X to appear in cell A1.

On the sheet where you want this to happen:

Select the sheet tab
Right click and select View code
Copy/paste the code below into the window that opens:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo Finish
If Target.Address = "$A$1" Then
With Target
If .Value = "X" Then
.Value = ""
Else
.Value = "X"
End If
End With
End If
Finish:
Application.EnableEvents = True
End Sub

Close the VB editor and return to Excel: ALT Q
Or, click the top "X" to close the window

Select cell A1 and the "X" will appear
Select any other cell
Select cell A1 again and the "X" will be removed
 
R

Ron Coderre

Maybe you can use something like this:

Cells A2:A5 are named: rngChkBxs
and formatted with the Wingdings font.

With the below method, Double-Clicking
on one of the rngChkBxs cells toggles the value
between a checked checkbox and an empty checkbox.

To put the code in the workbook:
Right-click on the sheet tab and select View Code

(That will open the VB Editor to the sheet's code module)

Copy the below code into that sheet module:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("rngChkBxs")) Is Nothing Then
With Target
Select Case Asc(.Value)
Case Is = 254:
'Enter a Check Box
.Value = Chr(168)
Case Else:
'Enter an Empty Box
.Value = Chr(254)
End Select
End With
End If
End Sub

Test the rngChkBxs cells by Dbl-Clicking them.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
R

Ron Coderre

Darn....the code comments got reversed.

Chr(168) is the empty box
Chr(254) is the checked box.

Since I was editing the code, anyway,
I also tweaked it a bit:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("rngChkBxs")) Is Nothing Then
With Target
Select Case Asc(CStr(.Value) & " ")
Case Is = 254:
'Is a Checked Box, enter an Empty Box
.Value = Chr(168)
Case Else:
'Is anything else, enter an Checked Box
.Value = Chr(254)
End Select
End With
End If
End Sub


Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Top