Double Click Clear macro

John Caines

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,155
Office Version
  1. 2019
Platform
  1. Windows
Hello All.

I'm just trying to get a double click clear Macro to run on my worksheet.

The formula I have is;
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim RowsToClear     As String
    Dim x, i As Long, y
    Dim AllRowsAddress  As String
    Dim RowAdjust       As Long
    Const RowsToExclude As String = "{1,2,3,19,20,36,37,54}"
    AllRowsAddress = "C5:C44" '<<=== adjust to suit
    
    RowsToClear = Application.InputBox("Enter the rows separated by comma", "Clear Rows", "1,7,9", Type:=1 + 2)
    
    If RowsToClear = "False" Then Exit Sub
    
    RowAdjust = Range(AllRowsAddress).Row - 1
    
    If MsgBox("Are You Sure You Want To Delete These ROWs " & vbLf & RowsToClear & " ?", vbYesNo + vbInformation) = vbYes Then
        x = Split(RowsToClear, ",")
        On Error Resume Next
        For i = 0 To UBound(x)
            y = Evaluate("=match(" & CLng(x(i)) & "," & AllRowsAddress & ",0)")
            If Not IsError(y) Then
                Application.Intersect(Range("c:q"), Rows(x(i) + RowAdjust)).ClearContents
            End If
        Next
        On Error GoTo 0
    End If
    
End Sub
I'm sure this was written some time ago by a guy called Krishnakumar.
Many thanks again.
I don't know if I've inadvertently altered it,, not sure.
I'll link to a sample sheet.

Basically I would just like it to work clearing rows of data,, C5:Q5
when you double click a pop up appears,,,
& you can manually enter the row numbers (Row numbers by the numbers in Column B (B5:B44),, not the cell row numbers)

If anybody understands VB here, please,,
It's above me.

Here's the link to a test spreadsheet.
http://dl.dropbox.com/u/16052166/Double_Click_Macro.xlsm

Many thanks for all your time
I hope somebody can fix it.
Best Regards
John Caines
 
Sort by date Sort by votes
Hello all.
I've tried to really see why this isn't working,, I just can't see it :-(
Actually,, there is 1 error in the code I quoted,,
the line that says;
Code: 
 AllRowsAddress = "C5:C44" '<<=== adjust to suit
/code]
it should be;
[code]
 AllRowsAddress = "B5:B44" '<<=== adjust to suit
The download link has the changed code in.
http://dl.dropbox.com/u/16052166/Double_Click_Macro.xlsm

I do have an old sheet with the formula working!
So,, I copied the formula into notepad++
My new sheet,, I copied that formula into another text file in notepad ++

I used the "Compare" plugin ,(Which I read about on google) ;
http://www.addictivetips.com/window...codes-and-text-files-in-notepad-with-compare/
A great feature to see what was the difference between the 2 files.
here's a shot;


It's so strange,,,
They are the same!,, (except for 2 lines of code,, which I've altered to suit).

Still,,, in the sample spreadsheet,, the download link I've provided it still isn't working.???
Can anybody shed some light on this for me please,,
I really can't see why the spreadsheet isn't working,,

it must be something simple I've overlooked,, I just don't get it :-(

Many thanks for all your time.
I hope somebody can advise.
A very frustrated,
John Caines
 
Upvote 0
Not even sure what I've done??
I've got it working,,?????

It must be how I've put the formula into VB?

Hey,, at least it's working now,,
I'll mark this as solved.

Many thanks
John Caines

(Still not sure what I've done,,but hey, it's working :-)
 
Upvote 0
John

I see you've got it sorted and I don't know if it would have helped but anyway, you can add this to cancel the double-click.
Code: 
Cancel = True
 
Upvote 0
Hello Norie, thanks for your reply,,,

Thing is,, I'm not quite sure what you mean..?
Can you post the full code as an example please?
The only Cancel word I saw in the formula was in the title;
So i just tried changing it,, & it all went red :-(
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel = True)

I thought you meant change that to cancel=true? :-(
My VB skill is just above 0
So you mean put this in the code somewhere...but where?

If the code then is altered so it doesn't work on a double click,, so you mean I'd have to insert a module,, and assign this as a macro,, so the pop up would appear on an assigned macro button? So I would click the button?

Many thanks for your reply anyway

All the best
John Caines
 
Upvote 0
John

It would just be the first line of the code.
Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim RowsToClear     As String
    Dim x, i As Long, y
    Dim AllRowsAddress  As String
    Dim RowAdjust       As Long
    Const RowsToExclude As String = "{1,2,3,19,20,36,37,54}"
 
    Cancel = True

It's cancels the double-click action not the code triggered by it.

But if the code is working without it I don't think it really matters.:)

PS I didn't have a good proper look at the post when I saw it was solved - got distracted trying to work out how you got the formatting thng in Notepad++ as well.

Tried every menu, apart from Plugins, and there it was top of the list.

Still can't get the little warning symbols though.:sad:
 
Upvote 0
Hi Nori,,
many thanks,,
As to Notepad++,, I've only just started using it.
I was using textpad for ages,, as you could right click to open webpage hyperlinks,,,,

For ages Notepad++ didn't have this feature,,
but I noticed now the do,, on a double click
(Preferences/Language Menu/Tab Settings/Clickable Link Settings,, choose enable

As to the warning icons,,
I have;
Notepad++ 5.9.8
http://www.filehippo.com/download_notepad/changelog/
http://notepad-plus-plus.org/download/v5.9.8.html

Maybe it's a slight improvement on an older version?
I haven't altered any settings .
All the best
JC
 
Last edited:
Upvote 0
John

It's their in the version I've got, I wasn't seeing the warning signs because I was comparing completely different things.

I've updated anyway.:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Pass value to a double click procedure
Replies
2
Views
190
FryGirl
F
M
  • Solved
Userform image event handler for date picker?
Replies
4
Views
305
mdeanh5252
M
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
486
John_w
J
R
  • Question Question
VBA Code Works in Visual Basic Coder, but not with Command Button in Excel
Replies
3
Views
330
rheintz
R
R
  • Question Question
VBA to automate calculation
Replies
1
Views
244
HighAndWilder
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top