Worksheet_Change not repetitive

  • Thread starter Thread starter David T.
  • Start date Start date
D

David T.

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.
 
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
 
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
 
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)
 
Back
Top