### Tags: appear, cell, click, clicking, code, double, excel, folowing, microsoft, msdn, simply, software, user

# How do I make an X appear in a cell by simply double clicking it?

On Microsoft » Microsoft Excel

4,977 words with 3 Comments; publish: Thu, 22 May 2008 08:36:00 GMT; (30662.01, « »)

I want to allow the user to double click a cell and have a "X" appear

and to double click again and have the "X" go away. The folowing code

does this for column B however I need to do this for columns C and H

and not have any requirement that data be in any of the other cells.

I appreciate any help on this.

Thanks,

Robert

Private Sub Worksheet_BeforeDoubleClick( _

ByVal Target As Excel.Range, Cancel As Boolean)

With Target

If Not Intersect(.Cells, Range("B1:B" & Range("A" & _

Rows.Count).End(xlUp).Row)) Is Nothing Then

.Value = IIf(.Value = "", "X", "")

Cancel = True

End If

End With

End Sub

*http://excel.todaysummary.com/q_microsoft-excel_229745.html*

All Comments

Leave a comment...

- 3 Comments
- Something like this should work...
Private Sub Worksheet_BeforeDoubleClick( _

ByVal Target As Excel.Range, Cancel As Boolean)

Dim rng As Range

Set rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)).EntireRow

Set rng = Union(Intersect(Columns("B"), rng), _

Intersect(Columns("C"), rng), _

Intersect(Columns("H"), rng))

With Target

If Not Intersect(rng, Target) Is Nothing Then

.Value = IIf(.Value = "", "X", "")

Cancel = True

End If

End With

End Sub

--

HTH...

Jim Thomlinson

"robnsd" wrote:

> I want to allow the user to double click a cell and have a "X" appear

> and to double click again and have the "X" go away. The folowing code

> does this for column B however I need to do this for columns C and H

> and not have any requirement that data be in any of the other cells.

> I appreciate any help on this.

> Thanks,

> Robert

>

> Private Sub Worksheet_BeforeDoubleClick( _

> ByVal Target As Excel.Range, Cancel As Boolean)

> With Target

> If Not Intersect(.Cells, Range("B1:B" & Range("A" & _

> Rows.Count).End(xlUp).Row)) Is Nothing Then

> .Value = IIf(.Value = "", "X", "")

> Cancel = True

> End If

> End With

> End Sub

>

#1; Thu, 22 May 2008 08:37:00 GMT

- Something like this should work...
- One way:
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim myRng As Range

With Me

Set myRng = .Range("B1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)

End With

Set myRng = Union(myRng.Resize(, 2), myRng.Offset(0, 6))

With Target

If Not Intersect(.Cells, myRng) Is Nothing Then

.Value = IIf(.Value = "", "X", "")

Cancel = True

End If

End With

End Sub

It still uses column A to find the last row.

robnsd wrote:

> I want to allow the user to double click a cell and have a "X" appear

> and to double click again and have the "X" go away. The folowing code

> does this for column B however I need to do this for columns C and H

> and not have any requirement that data be in any of the other cells.

> I appreciate any help on this.

> Thanks,

> Robert

> Private Sub Worksheet_BeforeDoubleClick( _

> ByVal Target As Excel.Range, Cancel As Boolean)

> With Target

> If Not Intersect(.Cells, Range("B1:B" & Range("A" & _

> Rows.Count).End(xlUp).Row)) Is Nothing Then

> .Value = IIf(.Value = "", "X", "")

> Cancel = True

> End If

> End With

> End Sub

--

Dave Peterson

#2; Thu, 22 May 2008 08:38:00 GMT

- One way:
- On Feb 6, 12:49 pm, Dave Peterson <peter....excel.todaysummary.com.verizonXSPAM.net> wrote:
> One way:

> Option Explicit

> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

> Cancel As Boolean)

> Dim myRng As Range

> With Me

> Set myRng = .Range("B1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)

> End With

> Set myRng = Union(myRng.Resize(, 2), myRng.Offset(0, 6))

> With Target

> If Not Intersect(.Cells, myRng) Is Nothing Then

> .Value = IIf(.Value = "", "X", "")

> Cancel = True

> End If

> End With

> End Sub

> It still uses column A to find the last row.

>

>

> robnsd wrote:

> > I want to allow the user to double click a cell and have a "X" appear

> > and to double click again and have the "X" go away. The folowing code

> > does this for column B however I need to do this for columns C and H

> > and not have any requirement that data be in any of the other cells.

> > I appreciate any help on this.

> > Thanks,

> > Robert

> > Private Sub Worksheet_BeforeDoubleClick( _

> > ByVal Target As Excel.Range, Cancel As Boolean)

> > With Target

> > If Not Intersect(.Cells, Range("B1:B" & Range("A" & _

> > Rows.Count).End(xlUp).Row)) Is Nothing Then

> > .Value = IIf(.Value = "", "X", "")

> > Cancel = True

> > End If

> > End With

> > End Sub

> --

> Dave Peterson- Hide quoted text -

> - Show quoted text -

Thanks guys. The X box works great.

Robert

#3; Thu, 22 May 2008 08:39:00 GMT

- On Feb 6, 12:49 pm, Dave Peterson <peter....excel.todaysummary.com.verizonXSPAM.net> wrote: