why does this function repeat?

[vbcad]

I am new at VBA with excel so bear with me. The following code works, however it appears to run several times. I have tried in vain to resolve this issue. When I click in cell A2 the code runs several times in a row, almost like it is in a counting loop.

Sub clicknow()
' module code
' clicknow Macro
'
    Range("A18").Select
    Selection.Copy
    Range("a2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        SendKeys "{ESC}"
 End Sub

' sheet1 code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then clicknow
End Sub

 

[SkipVought]

Hi,

Try this, sans tyhe Select Method...

Sub clicknow()
' module code
' clicknow Macro
'
    Range("A18").Copy
    Range("a2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        SendKeys "{ESC}"
 End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Sub clicknow()
Application.EnableEvents = False
[i]your actual code here[/i]
Application.EnableEvents = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fumei]

The Sub is Worksheet_SelectionChange.

Change. ANY selection change will fire the Sub.

So.....

Range("A18").Select
' this is a change so SelectionChange fires again
Selection.Copy
Range("a2").Select
' this is a change so SelectionChange fires again



faq219-2884

Gerry
My paintings and sculpture

 

[vbcad]

thanks that was fast!

 

[fumei]

And just as a BTW...

your subject asked why does this function repeat.

It is not a function, it is a Sub.

faq219-2884

Gerry
My paintings and sculpture