Putting a Check or an X in cell

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
Is there a macro that when i click in a cell it will put a check or and X in the cell?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Easier to do it on a double_click... right click the sheet tab, and insert this code:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Value = "X"
    Cancel = True
End Sub

Why not use a Check Box from the Forms Toolbar, though?
 

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
Where can I assign it to the cells that I want it to work for? I want the check box to be the size of the cell.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

Like this:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Intersect(Range("Check"), Target) Is Nothing Then
    
        Exit Sub
        
    End If
    
    If Target.Value = "X" Then
    
        Target.Value = ""
        
    Else
    
        Target.Value = "X"
        
    End If
    
    Cancel = True
    
End Sub
where Check is a named range containing all the cells where you want this to apply... cells need not be contiguous. I tweaked it so that you can toggle the X...
 

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
Works perfectly, is there a way that I can assign a cell that if that's X'd that it will auto X all in that column
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

Umm, seems a little dangerous... but this'll do it...
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Intersect(Range("Check"), Target) Is Nothing Then
    
        Exit Sub
        
    End If
    
    If Target.Value = "X" Then
    
        Columns(Target.Column).Value = ""
        
    Else
    
        Columns(Target.Column).Value = "X"
        
    End If
    
    Cancel = True

end sub
 

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
That didn't work As I thought, I am going to use your first code and use an if statement for the other thing.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Yeah, I didn't think it would...perhaps this is more along the lines of what you were expecting:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Intersect(Range("Check"), Target) Is Nothing Then
    
        Exit Sub
        
    End If
    
    If Target.Value = "X" Then
    
        Target.Value = ""
    
        Intersect(Rows(Range("check_rows").Row & ":" & Range("check_rows").Row + Range("check_rows").Rows.Count - 1), Columns(Target.Column)).Value = ""
        
    Else
    
        Target.Value = "X"
    
        Intersect(Rows(Range("check_rows").Row & ":" & Range("check_rows").Row + Range("check_rows").Rows.Count - 1), Columns(Target.Column)).Value = "X"
        
    End If
    
    Cancel = True
    
End Sub
where check_rows is a named range (single column) of discreet length which contains the rows where you want this to be applied
 

Emoncada

Active Member
Joined
Mar 23, 2005
Messages
409
Example THis is what I have

If G12 is X'd then G13:G24 get X'd
If H12 is X'd then H13:H24 get X'd

If J12 is X'd then J13:J24 get X'd
If K12 is X'd then K13:K24 get X'd

If M12 is X'd then J13:J24 get X'd
If N12 is X'd then K13:K24 get X'd

Then

If G29 is X'd then G30:G40 get X'd
If H29 is X'd then H30:H40 get X'd

If J29 is X'd then J30:J40 get X'd
If K29 is X'd then K30:K40 get X'd

If M29 is X'd then M30:M40 get X'd
If N29 is X'd then N30:N40 get X'd

I hope that gives you more of an Idea of what i am looking at.
 

Forum statistics

Threads
1,136,926
Messages
5,678,606
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top