+ Reply to Thread
Results 1 to 6 of 6

Change cell value when right-clicked/double-clicked

  1. #1
    Registered User
    Join Date
    02-01-2005
    Posts
    62

    Question Change cell value when right-clicked/double-clicked

    User inputs data into a cell. I have the "default" value of that cell in another cell. I would like the user to be able to either right-click or double-click on the input cell to restore the default value.

    Is that possible? Thanks in advance.

  2. #2
    Haukwa
    Guest

    Re: Change cell value when right-clicked/double-clicked

    Grime,

    I recently developed a similar function. I used conditional formatting
    to save the default value. So long as the target cell value matched
    the default value, the target cell text was black. If the target cell
    value differed from the default value, the target cell text was red.

    I then assigned VBA code to the right-click event that replaced the
    target cell value with the default value. I can send you/post the code
    next week (it's at work).

    All that to say, it can be done...and it is quite handy.

    Gerry


    grime wrote:
    > User inputs data into a cell. I have the "default" value of that cell
    > in another cell. I would like the user to be able to either
    > right-click or double-click on the input cell to restore the default
    > value.
    >
    > Is that possible? Thanks in advance.
    >
    >
    > --
    > grime
    > ------------------------------------------------------------------------
    > grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
    > View this thread: http://www.excelforum.com/showthread...hreadid=476434



  3. #3
    Norman Jones
    Guest

    Re: Change cell value when right-clicked/double-clicked

    Hi Grime,



    Perhaps you could use the built in Data Validation feature.



    On the Data Validation settings tab, set the 'Allow' box value to 'List'
    and in the 'Source' box either type the default value or select the cell
    containing the default value. Then, to enable alternative user entry,
    deselect the 'Show error alert' option on the 'Error Alert' tab.



    This will permit the user to make any entry, but also allow the user to
    enter the default value from the DV dropdown.



    ---
    Regards,
    Norman



    "grime" <grime.1wx86d_1129341905.0663@excelforum-nospam.com> wrote in
    message news:grime.1wx86d_1129341905.0663@excelforum-nospam.com...
    >
    > User inputs data into a cell. I have the "default" value of that cell
    > in another cell. I would like the user to be able to either
    > right-click or double-click on the input cell to restore the default
    > value.
    >
    > Is that possible? Thanks in advance.
    >
    >
    > --
    > grime
    > ------------------------------------------------------------------------
    > grime's Profile:
    > http://www.excelforum.com/member.php...o&userid=19227
    > View this thread: http://www.excelforum.com/showthread...hreadid=476434
    >




  4. #4
    damorrison
    Guest

    Re: Change cell value when right-clicked/double-clicked

    sure, here you go

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

    'sets the range for code to work in, this one works only in column A

    If Union(Range("$A:$A"), Target).Address = Range("$A:$A").Address Then

    ' selects the active cell and then moves 10 columns to the right,
    selects that cell and copies it

    ActiveCell.Offset(0, 10).Range("A1").Select
    Selection.Copy

    'moves back to the left 10 columns and pastes

    ActiveCell.Offset(0, -10).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    'moves down to the cell below

    ActiveCell.Offset(1, 0).Range("A1").Select
    End If


    End Sub

    right click on the sheet tab, select view code, from the dropdown menu
    where it says ,general, select worksheet, enter the code above
    Dave


  5. #5
    Registered User
    Join Date
    02-01-2005
    Posts
    62
    Quote Originally Posted by damorrison
    sure, here you go
    Thanks Dave. Exactly what I was looking for. Works perfectly.

    And thanks to the others that replied. Much appreciated.

  6. #6
    Haukwa
    Guest

    Re: Change cell value when right-clicked/double-clicked

    Grime,

    Here's the code I used...similar technique to damorrison. I saved the
    default value in the conditional formatting area.

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
    Boolean)
    Dim intersect As Range
    Set intersect = Application.intersect(Range("VARIABLES1"), Target)

    If intersect Is Nothing Then
    Cancel = False
    ElseIf intersect.Address = Target.Address Then
    Cancel = True
    If Target.Formula <> Target.FormatConditions(1).Formula1 Then
    Target.Formula = Target.FormatConditions(1).Formula1
    End If

    End If


    End Sub


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1