vba changing cell text

Using Excel 2000

Problem:

I want to change the text/value of a cell programmatically.

For testing purposes, i've used the following formula.
=IF(Q40>AH40,"YES","NO")

But instead of returning a "YES" or a "NO" to the current cell containing the formula, i'd like to change the value of a different cell (increment the current value by 1).

Issue:

Using a VBA function is not working, it reaches the code line that populates the cell with the new incremented value, then nothing. It neither updates the cell nor finishes the function.

Code:

'Neither of the following will work
'lngStock1 is a long variable = to the incremented value

Worksheets("List").Cells(1, "B").Value = lngStock1
Worksheets("List").Cells(1, "B").Formula = "=" & lngStock1 & "+1"
Worksheets("List").Cells(1, "B").Value = "4"

'Placing that same code inside a button click does work only
'if the user clicks the button however

Private Sub cmdTWH_Click()
Worksheets("List").Cells(lngStock1, "L").Formula = "=" & lngStock1 & "+1"
Worksheets("List").Cells(lngStock2, "L").Formula = "=" & lngStock2 & "+1"

End Sub



Any help would be appreciated because my boss is getting impatient!!

~Tony Huss

Well, i came up with a makeshift solution to my problem.

I have the user press a button to activate the sheet. I used Application.OnTime to create a timer and a global variable.

The global variables hold information that will used to update other cells.
The timer is periodically called and uses a simple "if" statement to test the global variables.

If glngS1 > 0 And glngS2 > 0 Then
'do code, update cells
'reset globals to zero
glngS1=0
glngS2=0
end if

This is not the most elegant way to solve my original problem, but sometimes you have to go with what works and hope a better solution comes along.

Included is a nice link to an example of using the Application.OnTime

Planet source code OnTime Example

~Tony