Solved

Double Click Event in Excel

Posted on 2011-03-15
3
383 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
  • 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 300 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

776 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