Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Double-Click Action (Excel 2003)

    Is it possible to change what a right, double-click does in Excel?

    I have a range of cells (O3:Z45). I'd like to double-click a cell in this range and have it change cell color. That would be enough. But, if the code to make that happen could be easily modified to do more, then I would like it to.

    What would really be neat is if double-clicking the cell one time makes the cell shade (yellow), double-click the same cell again and it shades (gray). A third double-click and it's back to the default.

    Why? Setting up a vendor file where the vendors and other pertinent info is listed in columns B:N. Monthly disbursements listed in columns O:Z. Once an invoice is received, the amount is entered into the appropriate cell, when the check is run, we would double-click the amount and the cell would shade yellow. When the check is actually mailed (or cleared), we would double-click again to shade gray. Very easy to look at the sheet and tell who's been paid, etc.

    This describes the "look" I'm after. If there's a better way to accomplish this, I am certainly open to suggestions.

    Thanks,
    Attached Files Attached Files
    - Ricky

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

    Re: Double-Click Action (Excel 2003)

    You can define a double-click action, but keep in mind that the standard action is to edit the cell. You can disable this, but that is counter-intuitive.

    To create a double-click action and to disable the default action:
    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Enter or paste the following code:
    <code>
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target.Interior
    Select Case .ColorIndex
    Case 36
    .ColorIndex = 15
    Case 15
    .ColorIndex = xlColorIndexAutomatic
    Case Else
    .ColorIndex = 36
    End Select
    End With
    Cancel = True
    End Sub
    </code>
    Notes:

    1) To enable editing the cell by double clicking, comment out the line Cancel = True.
    2) The code as is applies to all cells. You could limit it to a certain range, if desired.

    You could also use the Worksheet_BeforeRightClick event.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Double-Click Action (Excel 2003)

    Hans,

    Thanks, I appreciate that. I'm going to use the "Worksheet_BeforeRightClick event" method.
    - Ricky

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Double-Click Action (Excel 2003)

    It should be noted that the BeforeDoubleClick event routine applies to a LEFT double click, not a right double click as originally asked. I do not see a way to affect a right double click (at least on XL2K).
    Legare Coleman

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

    Re: Double-Click Action (Excel 2003)

    Perhaps I should have mentioned that explicitly (it was implicit in the mention of editing the cell as default action.)

Posting Permissions

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