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.
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
Thanks Dave. Exactly what I was looking for. Works perfectly.Originally Posted by damorrison
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks