Solved

Clear Contents on Double Click

Posted on 2014-12-11
5
261 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
 

Author Closing Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
A short article about problems I had with the new location API and permissions in Marshmallow
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now