Change cell value when right-clicked/double-clicked

Change cell value when right-clicked/double-clicked

Post by grim » Sun, 16 Oct 2005 10:29:16


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.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

 
 
 

Change cell value when right-clicked/double-clicked

Post by Haukw » Sun, 16 Oct 2005 12:07:42

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

 
 
 

Change cell value when right-clicked/double-clicked

Post by Norman Jon » Sun, 16 Oct 2005 13:06:37

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" < XXXX@XXXXX.COM > wrote in
 
 
 

Change cell value when right-clicked/double-clicked

Post by damorriso » Sun, 16 Oct 2005 17:02:51

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
 
 
 

Change cell value when right-clicked/double-clicked

Post by grim » Sun, 16 Oct 2005 23:27:46


damorrison Wrote:

Thanks Dave. Exactly what I was looking for. Works perfectly.

And thanks to the others that replied. Much appreciated.


--
grime
------------------------------------------------------------------------
grime's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/
 
 
 

Change cell value when right-clicked/double-clicked

Post by Haukw » Tue, 18 Oct 2005 21:52:47

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