Solved

Clear Contents on Double Click

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

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

747 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

13 Experts available now in Live!

Get 1:1 Help Now