Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct issues SteveDB1 Excel Worksheet Functions 25 June 3rd 09 04:58 PM
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 06:12 PM
When double clicking on link in cell it doesn't go the cell. Brian Excel Discussion (Misc queries) 1 July 17th 06 05:11 PM
How do I double click a cell and jump to cell's referenced cell JerryJuice Excel Discussion (Misc queries) 2 September 10th 05 10:24 PM
make a cell empty based on condition mpierre Charts and Charting in Excel 2 December 29th 04 02:01 PM


All times are GMT +1. The time now is 01:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"