need help with this code==SOLVED

[mcleve]

Public Sub workbook_calculate()
If Range("A1") = "1" Then
Range("A2") = "running"
Else: Range("A2") = "stopped"
End If
End Sub

This code works if i hit the run sub button in the vba editor but when i close the editor and enter a new value in A1 the sub doesnt run.......... Any help would be appreciated!!!!

[ This Message was edited by: mcleve on 2002-05-19 19:25 ]

[AJ]

Try the following in your sheet module instead...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Range("A1") = "1" Then
Range("A2") = "running"
Else: Range("A2") = "stopped"
End If

End Sub

HTH
AJ

[Joe Was]

Your code is fine. I put it in a Sheet module (Sheet1) and built a form button an attached the code. If A1 is empty and I press the form button I get the right msg, if I add a "1" and press the form button I get the "Running" msg?

I think you want this to happen without the need to run your code.

"Public" only means that your code can be a accessed by other parts of Excel not that it will run automatically!

To run your code automatically you need Event code. If a cell value changes and you want to do something, you need an event trigger as your Sub Call.

If this is what you want try this in the Sheet module that you want the action to work, like: Sheet1. Hope this helps. JSW

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") = "1" Then
Range("A2") = "running"
Else: Range("A2") = "stopped"
End If
End Sub

[Adiv]

Why not place a formula in [A2] such as

=if(A1="1","Running","Stopped")

Hope this helps!

[Joe Was]

Adiv,

As you indicated a sheet formula is the better choice, it runs faster, takes less disk space and does the same action as the VBA code.

Some of the posts on this board are not the real problem, only a test question for the real problem. Or, some posters what to learn a new skill and not really solve a problem for an application. Then again some people's minds work better with code and others with sheet formulas.

In this case you are right the sheet formula is the right way to go, but then again that might not be the posters intent. To many posters "eat and run" they get a response and are in a hurry it get, whatever, to work and forget to thank or inform the person who responded. Others forget to check back or solve the problem on their own?

Not to imply that "Mcleve" is one of "those" but since we do not know what the intent is it is good to see you post the obvious solution. Some of us don't see the tree for the forest, sometimes. JSW

[Adiv]

Thanks for the advice. This is a very active forum chock full of interesting and challenging problems. Way cool!

[mcleve]

Hi,
And thanks for all the helpful advise!!!
Actually the problem is just a fraction of a much larger problem. But both solutions are a great help. I have another question I will post in another topic..
Thanks again,
MCLEVE