Help!

excel same value in two cells

 
Goto page 1, 2
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> New Users RSS
Next:  Help with Formula, returns #NAME!  
Author Message
pls help
External


Since: Jul 25, 2007
Posts: 1



PostPosted: Thu Jul 26, 2007 2:40 am    Post subject: excel same value in two cells
Archived from groups: microsoft>public>excel>newusers (more info?)

How can 2 cells have the same values, such that when I edit either cell the
change is reflected on both?
Back to top
CLR
External


Since: Nov 24, 2004
Posts: 1765



PostPosted: Thu Jul 26, 2007 9:18 am    Post subject: RE: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

This macro will do as you describe.........

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



"pls help" wrote:

> How can 2 cells have the same values, such that when I edit either cell the
> change is reflected on both?
Back to top
Pete_UK
External


Since: Jun 01, 2007
Posts: 510



PostPosted: Thu Jul 26, 2007 1:30 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Sandy,

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

On Jul 26, 4:37 pm, "Sandy Mann" wrote:
> Hi Chuck,
>
> 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
>
> sandyma...@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
> "CLR" wrote in message
>
>
>
>
>
> > 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 -
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Thu Jul 26, 2007 2:03 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Pete

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

On Thu, 26 Jul 2007 09:30:27 -0700, Pete_UK wrote:

>Hi Sandy,
>
>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
>
>On Jul 26, 4:37 pm, "Sandy Mann" wrote:
>> Hi Chuck,
>>
>> 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
>>
>> sandyma...@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>> "CLR" wrote in message
>>
>>
>>
>>
>>
>> > 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 -
>
Back to top
CLR
External


Since: Nov 24, 2004
Posts: 1765



PostPosted: Thu Jul 26, 2007 2:16 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Actually, in some "circles", 198 "Chuck's" might be condsidered more
desirable than just one.........I love my mirror)..... <G>

Vaya con Dios,
Chuck, CABGx3




"Gord Dibben" wrote:

> Pete
>
> 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
>
> On Thu, 26 Jul 2007 09:30:27 -0700, Pete_UK wrote:
>
> >Hi Sandy,
> >
> >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
> >
> >On Jul 26, 4:37 pm, "Sandy Mann" wrote:
> >> Hi Chuck,
> >>
> >> 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
> >>
> >> sandyma...@mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >> "CLR" wrote in message
> >>
> >>
> >>
> >>
> >>
> >> > 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 -
> >
>
>
Back to top
squenson
External


Since: Jul 26, 2007
Posts: 15



PostPosted: Thu Jul 26, 2007 2:45 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

It is not possible without using some sophisticated features of event
listening and macros. But one should ask the question: what would be the
benefits? You can refer the second cell from the first one -- something like
A2: =A1, so A1 and A2 will both have the same value and each time you change
A1, A2 will change.

pls help wrote:
>How can 2 cells have the same values, such that when I edit either cell the
>change is reflected on both?
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Thu Jul 26, 2007 2:57 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Chuck

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

On Thu, 26 Jul 2007 10:16:08 -0700, CLR wrote:

>Actually, in some "circles", 198 "Chuck's" might be condsidered more
>desirable than just one.........I love my mirror)..... <G>
>
>Vaya con Dios,
>Chuck, CABGx3
>
>
>
>
>"Gord Dibben" wrote:
>
>> Pete
>>
>> 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
>>
>> On Thu, 26 Jul 2007 09:30:27 -0700, Pete_UK wrote:
>>
>> >Hi Sandy,
>> >
>> >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
>> >
>> >On Jul 26, 4:37 pm, "Sandy Mann" wrote:
>> >> Hi Chuck,
>> >>
>> >> 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
>> >>
>> >> sandyma...@mailinator.com
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >> "CLR" wrote in message
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> > 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 -
>> >
>>
>>
Back to top
CLR
External


Since: Nov 24, 2004
Posts: 1765



PostPosted: Thu Jul 26, 2007 3:12 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

That seems cool...........thanks Gord.

Vaya con Dios,
Chuck, CABGx3



"Gord Dibben" wrote:

> Chuck
>
> 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
>
> On Thu, 26 Jul 2007 10:16:08 -0700, CLR wrote:
>
> >Actually, in some "circles", 198 "Chuck's" might be condsidered more
> >desirable than just one.........I love my mirror)..... <G>
> >
> >Vaya con Dios,
> >Chuck, CABGx3
> >
> >
> >
> >
> >"Gord Dibben" wrote:
> >
> >> Pete
> >>
> >> 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
> >>
> >> On Thu, 26 Jul 2007 09:30:27 -0700, Pete_UK wrote:
> >>
> >> >Hi Sandy,
> >> >
> >> >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
> >> >
> >> >On Jul 26, 4:37 pm, "Sandy Mann" wrote:
> >> >> Hi Chuck,
> >> >>
> >> >> 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
> >> >>
> >> >> sandyma...@mailinator.com
> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >>
> >> >> "CLR" wrote in message
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> > 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 -
> >> >
> >>
> >>
>
>
Back to top
squenson
External


Since: Jul 26, 2007
Posts: 15



PostPosted: Thu Jul 26, 2007 4:45 pm    Post subject: RE: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

On my version of Excel 2003, if I am in A1 and type 444 then press the down
arrow, A1 is being updated with the value from A2...

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)
Back to top
CLR
External


Since: Nov 24, 2004
Posts: 1765



PostPosted: Thu Jul 26, 2007 4:45 pm    Post subject: RE: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Interesting.....good catch.
Thanks for the heads-up.

Vaya con Dios,
Chuck, CABGx3



"squenson" wrote:

> On my version of Excel 2003, if I am in A1 and type 444 then press the down
> arrow, A1 is being updated with the value from A2...
>
> 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)
>
>
Back to top
Sandy Mann
External


Since: Jun 14, 2005
Posts: 1292



PostPosted: Thu Jul 26, 2007 7:43 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Chuck,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"CLR" wrote in message

> Interesting.....good catch.
> Thanks for the heads-up.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "squenson" wrote:
>
>> On my version of Excel 2003, if I am in A1 and type 444 then press the
>> down
>> arrow, A1 is being updated with the value from A2...
>>
>> 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)
>>
>>
>
Back to top
CLR
External


Since: Nov 24, 2004
Posts: 1765



PostPosted: Thu Jul 26, 2007 7:43 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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


"Sandy Mann" wrote:

> Chuck,
>
> 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
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "CLR" wrote in message
>
> > Interesting.....good catch.
> > Thanks for the heads-up.
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "squenson" wrote:
> >
> >> On my version of Excel 2003, if I am in A1 and type 444 then press the
> >> down
> >> arrow, A1 is being updated with the value from A2...
> >>
> >> 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)
> >>
> >>
> >
>
>
>
Back to top
Sandy Mann
External


Since: Jun 14, 2005
Posts: 1292



PostPosted: Thu Jul 26, 2007 8:37 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Chuck,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"CLR" wrote in message

> 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
Back to top
CLR
External


Since: Nov 24, 2004
Posts: 1765



PostPosted: Thu Jul 26, 2007 8:37 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Awesome Sandy.....thanks much for the lesson......every day it's something
new, (shaking my old gray head)..........maybe I can use this technique to
check out some of my larger macros that seem to take a long time to execute.

Thanks again,

Vaya con Dios,
Chuck, CABGx3




"Sandy Mann" wrote:

> Hi Chuck,
>
> 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
>
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "CLR" wrote in message
>
> > 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
>
>
>
>
Back to top
Sandy Mann
External


Since: Jun 14, 2005
Posts: 1292



PostPosted: Thu Jul 26, 2007 9:56 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Pete,

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Pete_UK" wrote in message

> Hi Sandy,
>
> 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
>
Back to top
Sandy Mann
External


Since: Jun 14, 2005
Posts: 1292



PostPosted: Thu Jul 26, 2007 10:15 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

The some is me, the Awe is chip <g>

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"CLR" wrote in message

> Awesome Sandy.....thanks much for the lesson......every day it's something
> new, (shaking my old gray head)..........maybe I can use this technique to
> check out some of my larger macros that seem to take a long time to
> execute.
>
> Thanks again,
>
> Vaya con Dios,
> Chuck, CABGx3
Back to top
Sandy Mann
External


Since: Jun 14, 2005
Posts: 1292



PostPosted: Thu Jul 26, 2007 10:17 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Thank you for confiming it Gord. Have you got any idea why it fires only 65
times if you leave the original printouts in the Immediate Window?

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca> wrote in message

> Pete
>
> 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
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Thu Jul 26, 2007 10:18 pm    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Not a clunk<g>

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"
wrote:

>Thank you for confiming it Gord. Have you got any idea why it fires only 65
>times if you leave the original printouts in the Immediate Window?
Back to top
Sandy Mann
External


Since: Jun 14, 2005
Posts: 1292



PostPosted: Fri Jul 27, 2007 12:36 am    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I think that you nailed it Gord, I tested:

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca> wrote in message

> Not a clunk<g>
>
> 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"
>
> wrote:
>
>>Thank you for confiming it Gord. Have you got any idea why it fires only
>>65
>>times if you leave the original printouts in the Immediate Window?
>
>
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Fri Jul 27, 2007 12:36 am    Post subject: Re: excel same value in two cells [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Interesting.

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"
wrote:

>I think that you nailed it Gord, I tested:
>
>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.
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> New Users All times are: Eastern Time (US & Canada)
Goto page 1, 2
Page 1 of 2

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum