Thanks for the update.

Gord

On Sun, 21 Oct 2007 19:44:00 -0700, Robert B.
wrote:

Mr. Dibben,

I figured it out - how to make it work in multiple columns.

Thanks again for your help.

Robert B.

"Gord Dibben" wrote:

Instead of dinking about with transparent triangles on cells, I would just use
doubleclick event code to run the macro.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Target.Address = "$A$1" Then
'For a range use
'If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then

On Error GoTo endit
Application.EnableEvents = False
Application.ScreenUpdating = False

With Target
.Value = "R"
.Offset(0, 1).Copy
.Offset(0, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks _
:=False, Transpose:=False
End With
Application.CutCopyMode = False
End If
Cancel = True
Range("O7").Select

endit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.

When you doubleclick on A1 the events take place.


Gord Dibben MS Excel MVP

On Sat, 20 Oct 2007 17:00:00 -0700, Robert B.
wrote:

I am trying to create a macro (attached to a transparent rectangle in cell A)
that will copy contents of cell B to Cell C and paste as a value. Problem I
have now is the transparent rectangle is duplicated each time it is activated
in cell A (macro does copy correctly to cell C) and I have to click on the
left side of cell A and slide over to the right side to activate transparent
rectangle (attached to macro) - any way to activate macro without clicking on
left side and sliding over the right.

See Example of Macro I have so far. Any help will be greatly appreciated.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 10/15/2007 by Robert
'

'
Application.ScreenUpdating = False
ActiveCell.FormulaR1C1 = "R"
ActiveCell.Offset(0, 1).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.OnKey "{ENTER}"
Range("o7").Select
ActiveCell.Select

End Sub


Robert B.