?
Solved

Double Click Event in Excel

Posted on 2011-03-15
3
Medium Priority
?
396 Views
Last Modified: 2012-05-11

I have successfully recreated a macro that this forum helped me with.  I have a table with two columns:

Column1: Date
Column 2: Total Outstanding Checks Issued on that Date

I download/link data from several sources into one of the sheets.  And the Total Outsanding uses a sumproduct formula to get the total for that date.

I created a macro (code attached) that creates a new workbook with the items that make up that total.   I've assigned it to a button.  

Instead, I'd like to be able to double click on the cell containing the total and do the same action.  Can someone lead me in the right direction?
Private Sub CommandButton2_Click()
    Dim i As Long
    Dim lastRow As Long
    Dim rRow As Long, cCol As Long
    Dim TxnDate As Date
    Dim rng As Range
    Dim wb1 As Workbook
    
    '~~> Change the range to the relevant range here
    If Intersect(Selection, Range("u20:u29")) Is Nothing Then
        'the intersect function checks to see if the selection exists in the given range
        MsgBox "Please select the cell in the correct range"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    'By setting ScreenUpdating to False at the Start of the macro, you will not only stop
    ' the constant screen flickering associated with recorded macro, but also greatly speed
    ' up the execution of the macro. The reason it speeds up code is because Excel no longer
    ' needs to repaint the screen whenever it encounters such commands as Select, Activate,
    ' LargeScroll, SmallScroll and many others.
           
    lastRow = Sheets("IssuedChecks").Range("A" & Rows.Count).End(xlUp).Row
    ' finds last row in IssuedChecks Worksheet
 MsgBox ("The last row is: " & lastRow)
    
   'This part tells the macro which row/date we are looking for
   rRow = Selection.Row
  MsgBox ("The current row is: " & rRow)
    
    TxnDate = Cells(rRow, 20).Value
    MsgBox ("We are looking for " & TxnDate)
        
    'Creating new table in memory?

    Set rng = Sheets("IssuedChecks").Range("A1:J1")
    
    
    For i = 2 To lastRow
        If Sheets("IssuedChecks").Range("c" & i).Value = TxnDate Then
           Set rng = Union(rng, Sheets("IssuedChecks").Range("A" & i & ":J" & i))
           'if the date matches, add this column to the open workbook
           
        End If
    Next i
    
    rng.Copy
     Set wb1 = Workbooks.Add
     wb1.Sheets(1).Activate
    ActiveSheet.Paste
    
    '~~> Change the file Name Here
    'wb1.SaveAs "\\adpsfs\Accounting\Bobby\MyFile.xls"
 
Call fitWidth

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Open in new window

0
Comment
Question by:BBlu
[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
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35144932
You could use the worksheet doubleclick handler:

You could also use the same code via a sub, personally I wouldn't recommend using a reference to the existing CommandButton2_Click rather move that code to a new sub in a normal code module and then in both cases reference that new sub

Chris
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    if not intersect(target, me.range("a1")) is nothing then
        newsubname
    end if

End Sub

Open in new window

0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1200 total points
ID: 35144945
Meant to say, replace "a1" with the cell containing your Total Outstanding and newsubname with the name you give your sub i.e.

1. Snippet in a normal code module.
2. Worksheet_BeforeDoubleClick in the worksheet code page as
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    if not intersect(target, me.range("a1")) is nothing then
        replaced_cb2_Click_event_handler_Sub
    end if

End Sub

3. Modified Private Sub CommandButton2_Click() as
Private Sub CommandButton2_Click()
    replaced_cb2_Click_event_handler_Sub
End Sub

Chris
Sub replaced_cb2_Click_event_handler_Sub()
    Dim i As Long
    Dim lastRow As Long
    Dim rRow As Long, cCol As Long
    Dim TxnDate As Date
    Dim rng As Range
    Dim wb1 As Workbook
    
    '~~> Change the range to the relevant range here
    If Intersect(Selection, Range("u20:u29")) Is Nothing Then
        'the intersect function checks to see if the selection exists in the given range
        MsgBox "Please select the cell in the correct range"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    'By setting ScreenUpdating to False at the Start of the macro, you will not only stop
    ' the constant screen flickering associated with recorded macro, but also greatly speed
    ' up the execution of the macro. The reason it speeds up code is because Excel no longer
    ' needs to repaint the screen whenever it encounters such commands as Select, Activate,
    ' LargeScroll, SmallScroll and many others.
           
    lastRow = Sheets("IssuedChecks").Range("A" & Rows.Count).End(xlUp).Row
    ' finds last row in IssuedChecks Worksheet
 MsgBox ("The last row is: " & lastRow)
    
   'This part tells the macro which row/date we are looking for
   rRow = Selection.Row
  MsgBox ("The current row is: " & rRow)
    
    TxnDate = Cells(rRow, 20).Value
    MsgBox ("We are looking for " & TxnDate)
        
    'Creating new table in memory?

    Set rng = Sheets("IssuedChecks").Range("A1:J1")
    
    
    For i = 2 To lastRow
        If Sheets("IssuedChecks").Range("c" & i).Value = TxnDate Then
           Set rng = Union(rng, Sheets("IssuedChecks").Range("A" & i & ":J" & i))
           'if the date matches, add this column to the open workbook
           
        End If
    Next i
    
    rng.Copy
     Set wb1 = Workbooks.Add
     wb1.Sheets(1).Activate
    ActiveSheet.Paste
    
    '~~> Change the file Name Here
    'wb1.SaveAs "\\adpsfs\Accounting\Bobby\MyFile.xls"
 
Call fitWidth

    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Closing Comment

by:BBlu
ID: 35151936
PERFECT, Chris.  And thanks for the additional notes and suggestions.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 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