Re: Worksheet_Change not repetitive
- From: "Chip Pearson" <chip@xxxxxxxxxxxx>
- Date: Sun, 17 Feb 2008 20:07:04 -0600
David,
The Application.EnableEvents property controls whether events are triggered (EnableEvents = True) or not triggered (EnableEvents = False). If you have access to the troublesome machine(s), you should test the value of this setting (enter "?Application.EnableEvents" and press Enter in the Immediate Window in VBA). If it comes up False, you need to examine the code to determine whether this setting is being turned to False but not restored to True. Events can be turned off only via code -- there is no UI control over the events.
It is fairly common to have code set up like the following:
Sub AAA()
On Error GoTo ErrH:
Application.EnableEvents = False
'''''''''''''''''''''''''''''
' code that raises an error.
'''''''''''''''''''''''''''''
Application.EnableEvents = True
ErrH:
End Sub
In such code, the error handler sends execution below the restoration of the EnableEvents setting and it remains False. This would, then, prevent any subsequent events from being handled.
Other things to examine are the case when a procedure turns off events and simply neglects to restore the property to true. Also, if an untrapped error occurs and the user ends code execution at the error message, the EnableEvents setting may never get restored.
It might be the case that the machines on which the events fail to occur have some other add-in loaded that responds to events and that code isn't restore the True value to EnableEvents.
I've never encountered the situation where Excel just simply fails to cause an event to occur. It is always the case, in my experience, that the EnableEvents property got set to False and was never reset to True.
--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
"David T." <DavidT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:0CC36C01-CDEA-41DB-BDCB-F30A93CBB7FD@xxxxxxxxxxxxxxxx
I am traveling and won't be in a position to try your solution but I don't
believe it is in right direction. On my machine and others in my department
the Worksheet_Change routine is repetitive and doesn't have issues. On
machines of other folks it acts like the first loop through shuts off macros
or shuts down the VBA code and won't execute on a subsequent worksheet change.
I am not that familiar with running code in a debug but that might be the
angle to try to isolate the issue.
Thanks for the help,
David
"Joel" wrote:
See if this works
Private Sub Worksheet_Change(ByVal Target As Range)
for each cell in target
if cell = "Search" Then
MsgBox cell
end if
next cell
End Sub
"David T." wrote:
> I have found the Worksheet_Change function to be a great way to provide
> criteria in Excel and return data from SQL tables. For example, typing > a
> city would return all of the address records for that city. Typing a
> different city would clear and return a new record set.
>
> The problem that I am having is that the Worksheet_Change function > works
> great on my machine and I am able to supply criteria in the target area > over
> and over and get different data each time. It doesn't, however, > repeatedly
> work on other machines. When sending the spreadsheet to other folks, > the
> first entry will result in retrieved data but changing the value in the
> spread won't update the data.
>
> I simplied the code down to a simple message box:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> If Not Application.Intersect(Target, Me.Range("Search")) Is Nothing > Then
> MsgBox Application.Intersect(Target, Me.Range("Search"))
> End If
>
> End Sub
>
> Whatever is typed in the target is echoed in a message box. My machine
> works fine, other only once.
>
> Any ideas on where I should look for differences would be appreciated.
.
- References:
- RE: Worksheet_Change not repetitive
- From: David T.
- RE: Worksheet_Change not repetitive
- Prev by Date: Re: Workbook Open
- Next by Date: Re: populate text boxes from data in worksheet
- Previous by thread: RE: Worksheet_Change not repetitive
- Index(es):
Relevant Pages
|
