Change cell value when right-clicked/double-clicked

G

grime

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.
 
H

Haukwa

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
 
N

Norman Jones

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.
 
D

damorrison

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
 
H

Haukwa

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top