SheetChange Event Fires more than expected

[Jessard]

Hi,

I am having trouble with the SheetChange event firing more than i would
expect. I have a OWC spreadsheet and a sheetchange event which updates a
cell in the 12th column. Relevant code is listed below. There is nothing in
the 12th cell to begin with.

Sub Spreadsheet1_SheetChange(Sh,Target)
Msgbox("sheetchanged R" & Target.Row & "C" & Target.Column)

Dim str

if ((Target.Row mod 9)=1) then
msgbox("in Target Row R" & Target.Row & "C" & Target.Column)

if Target.Column = 3 Then
msgbox("in Target Column R" & Target.Row & "C" & Target.Column)
Dim rowint, colint
rowint = Target.Row
colint = Target.Column

str = makeUpdate ("CompanyName",rowint,colint,0)
Spreadsheet1.activesheet.cells(rowint,12) = str
end if
end if

End Sub

Function makeUpdate(fieldName,row,col,num)

Dim sql
Dim sa
Dim changeOccurred
sa = false
changeOccurred = false
Dim whereIndex
whereIndex = -1
Dim alreadyIndex
alreadyIndex = -1
if Spreadsheet1.activesheet.cells(row-num,12) <> "" Then
sql = Spreadsheet1.activesheet.cells(row-num,12)
whereIndex = instr(sql," WHERE " & fieldName & " =")
sql = Left(sql,whereIndex)
sa = true
msgbox("in top if")
else
sql = "UPDATE Clients SET "
end if
dim endIndex
endIndex = -1
changeOccurred = true
alreadyIndex = InStr(sql," " & fieldName & " = ")
if alreadyIndex > 0 Then
endIndex = instr(alreadyIndex,sql,",")
if endIndex = 0 Then
endIndex = len(sql)
end if
sql = left(sql,alreadyIndex) & right(sql,len(sql)-endIndex)
sql = sql & " " & fieldName & " = '" &
Spreadsheet1.activesheet.cells(row,col) & "'"
else
if sa=true then
sql = sql & ", " & fieldName & " = '" &
Spreadsheet1.activesheet.cells(row,col) & "'"
else
sql = sql & " "& fieldName & " = '" &
Spreadsheet1.activesheet.cells(row,col) & "'"
end if
end if

if changeOccurred = true Then
makeUpdate = sql & " WHERE ClientID = '" &
Spreadsheet1.activesheet.cells(row-num, 2) & "'"
end if
End Function

Help would be much appreciated.

Thanks,
Jesse

 

[Alvin Bruney [MVP]]

The sheet change event fires 3 events. If i remember correctly, they are
before change, sheet change, and after change events. In addition, when you
add an item to the row, it also fires a change event.
the easiest way around this is to increment the value in column 12 for every
event that fires. Then you test for a value greater than 3 to determine if a
change occurred. the reason for this is when the sheet is loaded, the load
event fires a sheet change event (unless of course you turn events off on
the load). Finally, under load the sheetchange event can cause the
spreadsheet to hang because so many events are fired for data loads. one
optimization is to place code to exit the function if there is a value
greater than 3.



An excerpt from chapter 7 of my forth coming book addresses the issue.

Although this code works well for the vast majority of cases, there are
problems lurking behind the scenes that you should be aware of. One of the
issues with this approach is that it doesn't retrieve the changes for a cut
and paste using the mouse. This is because the paste event does not fire a
change event in the spreadsheet. As previously noted, the sheetchange event
only fires when the user tabs away from a selected cell. It is easy to see
why this is problematic because, in certain instances, the changed data may
not be retrieved. One fix for this scenario is to manually fire the
sheetchange event after the paste event has fired. Chapter 13 contains a
section dedicated to manually raising events and applies here as well.

Another issue is that the sheetchange can fire multiple times making the
code somewhat re-entrant. Scripting approaches typically do not support
protection of global variables in concurrent calls so this code will begin
to misbehave under load. Because most scripting languages do not internally
support threading and synchronization, there is no bullet-proof way to
repair the code.