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; (3065,914.06, « »)

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

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
    • 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
    • On Feb 6, 12:49 pm, Dave Peterson <peter....excel.questionfor.info.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