# Automatically changing cells in EXCEL

I have an MS EXCEL workbook sheet named 'Simulator' (which is always open) and it will receive DDE values from another application running on the same PC

I want to check every second if certain cells containing DDE values in my worksheet 'Simulator' change their value.

e.g. If the value in Cell A1 changes at any time then I want to compare the value in A1 to a known value to determine what to do ....

If A1 = 0 then do nothing

If A1 = 1 then increment B1 by 1 every second until B1 = C1

If A1 = -1 then decrement B1 by 1 every second until B1 = D1

I want to further extend this such that every second I check a range of cells from A1 to A100 and if any of them change then I want to put a value in the relative B cell in the same row

ie

If A43 = 0 then do nothing

If A43 = 1 then increment B43 by 1 every second until B43 = C43

If A43 = -1 then decrement B43 by 1 every second until B43 = D43
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Insert this in a module

``````Option Explicit
Public dTime As Date

Sub RunOnTime()
Dim ws As Worksheet, rw As Long
Set ws = ThisWorkbook.Worksheets("Simulator")
dTime = Now + TimeSerial(0, 0, 1)
Application.OnTime dTime, "RunOnTime"

For rw = 1 To 100
If ws.Range("A" & rw) = 1 Then
If ws.Range("B" & rw) < ws.Range("C" & rw) Then
ws.Range("B" & rw) = ws.Range("B" & rw) + 1
End If
ElseIf ws.Range("A" & rw) = -1 Then
If ws.Range("B" & rw) > ws.Range("D" & rw) Then
ws.Range("B" & rw) = ws.Range("B" & rw) - 1
End If
End If
Next rw
End Sub

Sub CancelOnTime()
Application.OnTime dTime, "RunOnTime", , False
End Sub
``````

And this in the ThisWorkbook module, to make the function start on workbook open.

``````Private Sub Workbook_Open()
RunOnTime
End Sub
``````

And/or insert a start and stop button on the sheet, see attached.
Macro-run-on-time.xlsm
0

Experts Exchange Solution brought to you by ConnectWise

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thank You for the xlsm sheet and the very elegant solution Ejgil. Much appreciated.
As a beginner to EXCEL macros I just need to get my head around the relationship between the macro and EXCEL. What I have immediately realised is that I have to save my workbook as an 'xlsm' rather than an 'xlsx'
I had one further question and hopefully won't disturb you any further ... Now i have found my way to the the code residing in Project - VBA Project
I am wondering what the 'Ark1 (Simulator) is as it only contains the line "Option Explicit" ?
0

Commented:
All my sheets are named Ark1, Ark2 etc. similar to Sheets1, Sheets2 etc. at start before renamed.
That is language dependant.
Option Explicit tells VBA that all variables must be declared with Dim, Public, Const.
I have set my VBA to automatically require variable declaration, select Menu Tools and Options.
Declaring variables prevent misspelling, and the autofill option can be used, type the first few characters, press Ctrl+Space and VBA inserts the rest, or show a list of possible options to select from if more than one.
If not declared, any misspelling makes a new empty variable, but with Option Explicit the code can not compile.
Declaring the sheet (Dim ws as Worksheet) tells VBA that ws is a worksheet, so typing ws. displays a list of what can be used with a worksheet, so I don't have to remember.
0

Author Commented:
Ejgil has provided a thorough and informative answer along with further supporting information to allow me to completely understand.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.