Worksheet_Change event that doesn’t!

Topic

New Reply

Hi all – hopefully someone can help with this intriguing problem?

I’ve spent the last week writing an Excel 2000 application that stores and saves data from an Industrial Control System.
Control signals are sent to a ‘Control Sheet’ in the Excel workbook. The idea is that when a cell changes on the ‘Control Sheet’, the value of the cell is examined by VBA and specific code modules are run depending on the value.

The system works perfectly when tested in-house by changing the cell values via mouse/keyboard. However, when the values are changed via the external link and sent to Excel via a dde link, the ‘Worksheet_Change’ event doesn’t trigger although the value of the cells change as expected.

I’m using the _Change event to keep system overheads to a minimum i.e., only checking the data when it has actually changed. I could set up a schedular that examines all the data on the control page every few seconds and tests if it has changed, but this seems very cumbersome when there is an event that should do it automatically!

Any idea’s?

Many thanks in advance.

Reply | Quote

Replies

I am definitely not the DDE expert, but I’ve tried out a small program in VB6, setting up a link to an Excel spreadsheet. To make it easy for myself, I’ve first created the Excel spreadsheet and named it test.xls. I started Excel and opened the test.xls spreadsheet and then I run the VB6 program, with only one line of code in the Form_Load event of Form1, reading DATAFILE = App.Path & “test.xls”. On this form I have one commandbutton and one textbox. The following code goes in the command1_click event:

Dim XlWb as Object
Set XlWb = GetObject(DATAFILE)
XlWb.ActiveSheet.Range(“A1”).Value = Text1.Text
Set XlWb = Nothing
End Sub

Running this VB6 program allows me, by clicking the command button, to put the contents of the Textbox in cell A1 of the activesheet of the workbook test.xls. This works fine. In the worksheet change event of the active sheet in test.xls, I put in the code:
Private Sub WorkSheet_Change(ByVal Target as Range)
Static q as integer
q = q + 1
if Target = Range(“A1”) then
Range(“A” & q + 1).Value = “reaction”
end if
End Sub

This works. The worksheet change event is triggered and “reaction” appears every time I click the button of the VB6 form.
If I change the line between if … end if of the VBA code by MsgBox “Reaction”, then something else happens: Excel starts flashing as if it demands the focus. However, I don’t know how to solve this. Sorry! Could it be that it depends on what you really want to execute? Filling cells with text can maybe be realized in the background, but lauching a messagebox from Excel, requires Excel to have the the focus.
This is maybe not much of help, but I just wanted to show you that in some cases, it has to work.

Reply | Quote

Hi Hans.

I also wrote a simple VB exercise, very similar to yours, just to prove that my brain hadn’t totally died and that I was doing something really stupid in Excel. It also worked exactly as yours did, and exactly as anticipated!

I have a horrible feeling that the problem is how the DDE link communicates between packages, somehow managing to by-pass the Change event. I’ll have a poke about on the Micrososft site and see if there’s any help there.

Many thanx for your time.

Regards,

Reply | Quote

It seems that the external link is somehow disabling the SheetChange event. When the value is updated is the new value taken into account in any calculations, (assuming you have formulae in the sheet). If re-calculation is taking place then perhaps you could use the SheetCalculate Event instead, unless of course it has other implications. (However if the sheet is unchanged as far as Excel is concerned, then it probably will not re-calculate).

Have you tried using the Workbook SheetChange event instead of the WorkSheet event (It occurs when cells in any worksheet are changed). After (before?) that is the Application SheetChange event. (I don’t know which, if any, of these has priority).

I assumed that sheet change events occurred even if the change was from an external link so I do not really know what is happening in your sheet. The above are only things you might like to try

Best of luck

Andrew C

Reply | Quote

Hi Andrew.

I don’t know about the DDE link disabling the Change event, but it certainly seems to be by-passing it somehow!

There is no formulae on the sheet, although I will try multiplying the data 1 and see if that forces the calculate event – it could be a very easy way round it if it does, and one way I hadn’t thought of!

I didn’t bother with trying the WorkBook_Change event as I have many sheets in the spreadsheet – all changing at different times!

Like you, I assumed it would work fine so long as the cell value changes. In the VB help file it says that any change internally or via an external link (which DDE certainly is!) will cause the Sheet-Change event to trigger. OH NO IT DOESN’T!!!!

Many thanx for your time; I’ll certainly have a go at the re-calculate method. Failing that, I’ll have to examine all the values on the control sheet every few seconds and test whether they have changed or not using VBA.

Regards.

Reply | Quote