Solved

Clear Contents on Double Click

Posted on 2014-12-11
5
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 47

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Introduction to Processes
Starting up a Project

707 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