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.

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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.

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