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

[robnsd]

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

 

[Guest]

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

 

[Dave Peterson]

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]

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.







--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks guys. The X box works great.

Robert