Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Range("a2").Value = Range("a1").Value
Else
If Target.Address = "$A$2" Then
Range("a1").Value = Range("a2").Value
Else
End If
End If
End Sub
Vaya con Dios,
Chuck, CABGx3
I would recommend that you replace the first line:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
by:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
You will also have to have a line:
Application.EnableEvents = False
before any changes are made to the sheet and:
Application.EnableEvents = True
before the End Sub to stop the code firing multiple times. For me in XL97
it fires 198 time before VBA thows in the towel.
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"CLR" <C...@discussions.microsoft.com> wrote in message
news:D97A50F7-79B4-4E9C...@microsoft.com...
> Interesting.....good catch.
> Thanks for the heads-up.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
Vaya con Dios,
Chuck, CABGx3
It was Chip that pointed out to me some time ago that one of my codes was
multi-firing because I missed those lines off although I could not see
anything happening on the sheet.
The code I used was:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Debug.Print "Chuck"
'Application.EnableEvents = False
If Target.Address = "$A$1" Then
Range("a2").Value = Range("a1").Value
Else
If Target.Address = "$A$2" Then
Range("a1").Value = Range("a2").Value
Else
End If
End If
'Application.EnableEvents = True
End Sub
Then I copied the entries in the Immediate Window and pasted them into a
sheet using Paste Special > Unicode Text to find that I had 198 Rows of
"Chuck"
What puzzles me is if, with the entries still in the Immediate Window, I
change the Debug.Print line to "Chuck2" I only get 65 "Chuck2". If I then
clear all the entries in the Immediate Window and run the code again I am
back to having 198 entries! Perhaps one of the real experts can explain that
to us.
If you un-comment the EnableEvents lines that you will only get one "Chuck"
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"CLR" <C...@discussions.microsoft.com> wrote in message
news:CBEBB48D-7647-4EA4...@microsoft.com...
I'm certainly no expert in VBA, but could you possibly have a NOW()
function somewhere on the sheet or something similar, which is causing
it to recalculate (and therefore change) to trigger the macro?
Just a thought ...
Pete
> Replace @mailinator.com with @tiscali.co.uk
>
> "CLR" <C...@discussions.microsoft.com> wrote in message
>
> news:CBEBB48D-7647-4EA4...@microsoft.com...
>
>
>
> > Hi Sandy.........
> > I bow to your much greater experience, and wish to learn all I can. In
> > this
> > instance, I intentionally left those lines off and do so as a general rule
> > anymore because I like to see things flash as the macro
> > progresses.....sort
> > of in lieu of a progress indicator. I was not aware that their absence
> > could
> > actually affect the operation of the macro itself. I use '97 also, and
> > see
> > no evidence of multiple firings here. How did you conclude that the macro
> > had fired 198 times?
>
> > Vaya con Dios,
> > Chuck, CABGx3- Hide quoted text -
>
> - Show quoted text -
No there is nothing in the sheet apart from the Data in A1 & A2. A1 entered
by me, A2 entered by the code.
I think that the Event Procedure is more a sort of "Worksheet_Change or
Refresh" than just a Worksheet_Change.
VBA entering the data in A2 is a Worksheet_Change so the code fires again.
On the second and subsequent runs although the data in cell is the same as
the code in entering it still fires the code again and so on..... or so says
Chip.
Did you try the code and get a different result?
--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"Pete_UK" <pash...@auditel.net> wrote in message
news:1185467427....@19g2000hsx.googlegroups.com...
Thanks again,
Vaya con Dios,
Chuck, CABGx3
I tested on a new workbook with nothing in it and received the multiple Chuck's
as Sandy did. 198 to be exact.
One Chuck with events disabled.
Gord
Vaya con Dios,
Chuck, CABGx3
--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:glkha351i0818agok...@4ax.com...
--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"CLR" <C...@discussions.microsoft.com> wrote in message
news:1BD9358C-8999-4512...@microsoft.com...
Some limitation to debug?
Debug and the Immediate Window are uncharted waters for me.
Gord
On Thu, 26 Jul 2007 18:17:59 +0100, "Sandy Mann" <sandy...@mailinator.com>
wrote:
If you're going to start disabling events you should trap for errors so you
re-enable if the code errors.
On Error Goto stoppit
Application.EnableEvents = False
'code here
stoppit:
Application.EnableEvents = True
Gord
Sub test()
For x = 1 To 10000
Debug.Print x
Next x
End Sub
and although you can see all 10,000 numbers being printed to the Immediate
Window, only the last 195 - 199 numbers are available in the window. It
would seem therefore that the Immediate Window has a limit of displaying
only the last 200 or so printouts.
Testing with the Event Procedure:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Debug.Print Target.Value
Target.Value = UCase(Target.Value)
End Sub
and entering Chuck in any cell produced 199 CHUCK's *not* as I would have
expected, one Chuck and 198 CHUCK's, (because the Debug.Print was *before*
the UCase call). I therefore assume that VBA gives up after *more* then 200
cycles but displays only the last 200.
--
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:conha31ec00ee4mj8...@4ax.com...
Nice to know we have nothing else to do except tinker.
We must have no "honey-do" list and/or can't get a Tee-time
On Thu, 26 Jul 2007 20:36:32 +0100, "Sandy Mann" <sandy...@mailinator.com>
wrote:
>I think that you nailed it Gord, I tested:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Target.Value = Target.Value + 1
End Sub
stops at 346 so I would gues that is where VBA is giving it up.
> We must have no "honey-do" list
I do have a "honey-do" list but I don't have a circular Tuit - I keep
telling my wife that I will do the tasks when I get a round Tuit