How do I make an X appear in a cell by simply double clicking it?
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 |
How do I make an X appear in a cell by simply double clicking it?
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 |
How do I make an X appear in a cell by simply double clicking it?
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 |
How do I make an X appear in a cell by simply double clicking it?
On Feb 6, 12:49 pm, Dave Peterson 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 |
All times are GMT +1. The time now is 06:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com