• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Double Click Event in Excel


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
BBlu
Asked:
BBlu
  • 2
1 Solution
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
 
BBluAuthor Commented:
PERFECT, Chris.  And thanks for the additional notes and suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now