Question

Attempting event-based data logging with DSDATA. When Excel cell data changes, I want to start data logging. Can you offer any hints?

Answer

There are two types of cell data changes (1) Editing the cell; (2) Non-editing cell data change.

(1) EDITING THE CELL - If the cell data changes based on an Excel edit, then Excel has a Visual Basic subroutine called "Worksheet_Change" that gets executed every time an edit occurs in Excel.
1. Select Tools --> Macro --> Visual Basic Editor.
2. In the “Project – VBAProject” window at left, double-click on “Sheet1 (Sheet1)” to open it up.
3. In the “General” combo box at the top left of this now open Sheet1, select “Worksheet.”
4. In the combo box at the top right select the subroutine called “Change.”
5. Now enter this sample code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target.Cells
If cell.Row = 1 And cell.Column = 1 Then
MsgBox "cell changed to " & cell.Value
End If
Next cell
End Sub
6. After entering the code, then, of course save it.
7. Switch back to the Worksheet view (normal Excel view).
8. Now if you edit Cell A1, you will get a message box (e.g. “cell changed to ”).
This is just a sample. It is used to fire up a message box, but it could be used to execute a macro.

(2) NON-EDITING CELL DATA CHANGE - If the cell data changes based on something other than an Excel edit (e.g. macro changes it; value read from PLC changes it) then Excel has a Visual Basic subroutine called "Worksheet_Calculate" that gets executed every time Excel recalculates all its formulas:
1. Select Tools --> Macro --> Visual Basic Editor
2. In the “Project – VBAProject” window at left, double-click on “Sheet1 (Sheet1)” to open it up.
3. In the “General” combo box at the top left of this now open Sheet1, select “Worksheet.”
4. In the combo box at the top right select the subroutine called “Caculate.”
5. Now enter this sample code:
Private Sub Worksheet_Calculate()
MsgBox "cell changed to " & Cells(1, 1).Value
End Sub
6. After entering the code, then, of course save it.
7. Switch back to the Worksheet view (normal Excel view).
8. Now if the data in Cell A1 changes, you will get a message box (e.g. “cell changed to ”).
This, too, is just a sample. It is used to fire up a message box, but it could be used to execute a macro.

---

FAQ Subcategory: None

This item was last updated on 2003-08-28
Document ID: 959

---

The information provided in the AutomationDirect knowledge base is provided "as is" without warranty of any kind. AutomationDirect disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall AutomationDirect or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if AutomationDirect or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion of limitation of liability for consequential or incidental damages so the foregoing limitation may not apply.