Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy to Specific Cell on Double click (Excel 2003)

    I am looking for some help on a specific worksheet.
    I would like to be able to double click on a cell ( that has data in it ) and when I do so, the contents of the cell double clicked is copied to cell E2
    Then be able to double click on another cell and have that cell's contents copied to cell E2
    The Code I'm working with is :
    Sub Worksheet_AfterDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rInt As Range
    Dim rCell As Range
    Set rInt =Intersect( Target, Range("C3:P312"))
    Set rCell = Range ("E2")
    If Not rInt Is Nothing Then
    rInt.Value = rCell
    End If
    Cancel = True
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy to Specific Cell on Double click (Excel 2003)

    Double-clicking a cell is used to edit the cell's formula; I wouldn't use it for other purposes. But if you really want to:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Intersect(Range("E2"), ActiveCell) Is Nothing Then
    If Not ActiveCell = "" Then
    Application.EnableEvents = False
    Range("E2") = ActiveCell
    Application.EnableEvents = True
    Cancel = True
    End If
    End If
    End Sub

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy to Specific Cell on Double click (Excel 2003)

    Thanks Hans. Excellent as always
    Question.
    How does the Application.EnableEvents = False affect the program?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy to Specific Cell on Double click (Excel 2003)

    The line Application.EnableEvents = False suspends the processing of other events that might be fired as a consequence of changing the value of E2. It's a precaution against generating an endless cascade of events. Event processing is turned on again immediately after setting the value of E2.

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy to Specific Cell on Double click (Excel 2003)

    Hans

    Thanks for the education

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •