Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Clear Contents on Double Click

Posted on 2014-12-11
5
Medium Priority
?
431 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 49

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 1000 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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Progress

618 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