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.

How about getting rid of the rectangle(s) completely and using the
worksheet's _BeforeDoubleClick event to do the work when you double-click a
cell in column A? Would that work for you? Here's code to do that. To put
the code into the proper place, right-click on the worksheet's name tab and
choose [View Code] from the popup list. copy and paste the code below into
the code module area that appears. From then on, when you double-click in
column A, whatever is in column be on the same row will be echoed into column
C on that row.

I'll 'warn' about this: sometimes when you click in the cell and try to
double click, the system may miss the double click, interpreting it as 2
single clicks. That happens to me sometimes. If the operation doesn't work
first try, try again. When I set something up like this, I try to remember
to first click in the cell and THEN, after a very short pause, do the actual
double-click.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
Target.Offset(0, 2) = Target.Offset(0, 1)
End If
End Sub

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

Oops, left out a line of code that you'll probably want - the Cancel = True
statement I've added here will keep you from ending up in the A cell in
editing mode when you double-click it:

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
Target.Offset(0, 2) = Target.Offset(0, 1)
Cancel = True
End If
End Sub


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

I like that bit Jerry.

Much shorter than the version I came up with.

Gord

On Sat, 20 Oct 2007 17:58:00 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

Target.Offset(0, 2) = Target.Offset(0, 1)

Finally!! It happened to someone else instead of me <g. I usually end up
writing 14 pages of code and someone comes along with a single, under 32
character worksheet function, to do the same darned thing.

"Gord Dibben" wrote:

I like that bit Jerry.

Much shorter than the version I came up with.

Gord

On Sat, 20 Oct 2007 17:58:00 -0700, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

Target.Offset(0, 2) = Target.Offset(0, 1)

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.

To: All

Thanks for the suggestions in code. I will try them today. I am just
learning how to use VBA. What I am trying to create is a entry / exit log.
Will let you know if it works.

Thanks again

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.

See also Jerry's post with the shorter code although it doesn't enter an "R" in
column A

You can fix that by combining the two.


Gord Dibben MS Excel MVP

On Sun, 21 Oct 2007 01:46:00 -0700, Robert B.
wrote:

To: All

Thanks for the suggestions in code. I will try them today. I am just
learning how to use VBA. What I am trying to create is a entry / exit log.
Will let you know if it works.

Thanks again

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.

Mr. Dibben,

The code below worked perfectly for 1 column. I have four columns in my
worksheet that need same. Is there a way to add more ranges to the code or
can I copy code and change ranges? I tried to copy and change ranges but
computer gave me an error about a name. I am not sure what that meant.

Any help Greatly appreciated

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.

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.

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.

Mr. Dibben,

Everything so far is working out great on the in/out log I am creating with
your help. I have one more question. I have made four worksheets for entry
/ exit. Is there are way to so out times from all four worksheets between
the hours of 18:00 and 06:00? Doing a data sort puts the hours from 00:00 to
24:00. I need it to sort from 18-0600

Robert

"Gord Dibben" wrote:

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.

Mr. Dibben,

The code for Copy Cell to Another cell using Macro is working great. I have
another question regarding same procedure. My question is: I have one
section on my worksheet were I want it to copy a cell to another cell and
paste after selecting something from a pull down list. Say Cell T has a pull
down list to pick from. When I select something from the list can excel
automatically copy the contents of cell T to say cell U? Can you add
another procedure to the sheet code already created or is this not possible?
See below for current sheet code used.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not
Application.Intersect(Range("n8:n207,q8:q207,y8:y2 07,ab8:ab207,ae8:ae207,ah8:ah207,ak8:ak207,an8:an2 07,aq8:aq207,at8:at207,aw8:aw207,az8:az207,bc8:bc2 07"),
Target) Is Nothing 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("as7").Select

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


Thanks for any help you can provide

Robert B.





"Gord Dibben" wrote:

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.

Add this procedure to the sheet module as a separate event.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo endit
If Target.Column = 20 Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Target.Value
End If

endit:
Application.EnableEvents = True
End Sub


Gord

On Tue, 6 Nov 2007 21:11:01 -0800, Robert B.
wrote:

Mr. Dibben,

The code for Copy Cell to Another cell using Macro is working great. I have
another question regarding same procedure. My question is: I have one
section on my worksheet were I want it to copy a cell to another cell and
paste after selecting something from a pull down list. Say Cell T has a pull
down list to pick from. When I select something from the list can excel
automatically copy the contents of cell T to say cell U? Can you add
another procedure to the sheet code already created or is this not possible?
See below for current sheet code used.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not
Application.Intersect(Range("n8:n207,q8:q207,y8:y 207,ab8:ab207,ae8:ae207,ah8:ah207,ak8:ak207,an8:an 207,aq8:aq207,at8:at207,aw8:aw207,az8:az207,bc8:bc 207"),
Target) Is Nothing 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("as7").Select

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


Thanks for any help you can provide

Robert B.





"Gord Dibben" wrote:

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.