Solved

Clear Contents on Double Click

Posted on 2014-12-11
5
288 Views
Last Modified: 2014-12-13
I want to clear contents of cells by double clicking another cell. I had some code already in my spreadsheet that was doing something when I double click and trying to get it to work for this instance.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

With Target
    If Not Intersect(.Cells, Me.Range("AQ10:AQ660")) Is Nothing Then
            Cancel = True

           Else

Clear contents contents of cells AR and AU
             
        End If
   
    End With

For example, if I double click AQ10, then clear contents of AR10 and AU10 or if double click AQ11, then clear contents of AR11 and AU11...does this make sense? Thanks!
0
Comment
Question by:tracyms
5 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40495427
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(ActiveCell, Range("AQ10:AQ660")) Is Nothing Then
       Cancel = True
    Else
        Target.Offset(0, -1).ClearContents
        Target.Offset(0, 1).ClearContents
    End If
End If

Open in new window

0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40495438
Try

    If Intersect(Target, Range("aq10:aq660")) Is Nothing Then
        Cancel = True
    Else
        Target.Offset(, 1).Resize(, 4).ClearContents
        Cancel = True
    End If

Open in new window

0
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 250 total points
ID: 40495442
it should be

Union(Target.Offset(, 1), Target.Offset(, 4)).ClearContents

Open in new window

0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 40495822
I suspect you only want to cancel the default action when you click column AQ, so:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Not Intersect(Target, Me.Range("AQ10:AQ660")) Is Nothing Then
        Cancel = True
        Cells(Target.Row, "AR").ClearContents
        Cells(Target.Row, "AU").ClearContents
    End If

End Sub

Open in new window

0
 
LVL 1

Author Closing Comment

by:tracyms
ID: 40498058
Both solutions worked - thanks!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A short article about problems I had with the new location API and permissions in Marshmallow
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question