VBA code Help

[Joneye]

Im looking for to go in cell A1 of a workbook, all the code is required to do is count 1 - 100 over a time say 100 seconds.

If possible can the code count up, then count down and keep repeating. But have a stop / off button?

Whats it for, im looking to automate a graph via it.

PS many thanks in advance.

 

[Joneye]

Bump

 

[kpark91]

Hello, Joneye.
I haven't tested but try this:

1) First put the value you want to count from in cell A1 of a worksheet
2) In the inputted worksheet module, copy+paste this code

Sub Test()
    Range("A1").Value = Range("A1").Value + 1
    If Range("A1").Value <= 100 Then
        Application.OnTime Now + TimeValue("00:00:01"), "Test"
    End If
End Sub

Note: Please keep in mind that you will be unable to do anything on excel until this is done running

 

[Joneye]

Its close, put the code in, created a button to run it, it counts up providing the button is clicked...

Then "The Macro "book1!test" can not be found - APPEARS as a pop up.

Feel free to advise.

ps excel 2003 here.

 

[kpark91]

Tyr this

Sub TimerCount()
    Dim prevTime#
Timer_Loop:
    DoEvents
    If Fix(Timer) - Fix(prevTime) = 1 Then
        Range("A1").Value = Range("A1").Value + 1
    End If
    prevTime = Timer
    If Range("A1").Value < 100 Then GoTo Timer_Loop
End Sub

It's very heavy on your CPU tho.

 

[Joneye]

OOO,

That works fine, ive created a button that goes up, what code would I need to make it stop which I can apply to a button?

 

[kpark91]

Hello, the code should stop when it reached 100 but if you want...

altho this is not a good practice, I think you should use something like

Public sw As Boolean
Private Sub btnStart_Click()
       Dim prevTime#
    sw = True
Timer_Loop:
    DoEvents
    If Fix(Timer) - Fix(prevTime) = 1 Then
        Range("A1").Value = Range("A1").Value + 1
    End If
    prevTime = Timer
    If Range("A1").Value < 100 And sw = True Then GoTo Timer_Loop
End Sub
 
Private Sub btnStop_Click()
    sw = False
End Sub

Using a global variable.

 

[Joneye]

Hi,

Many thanks entered all the code in, sadly its not functioning. I copied and pasted all data to new sheet (tab1) then made a button and sadly can not attach the vba to the button.

feel free to assist?

 

[kpark91]

It's working on my worksheet.

Have you used ActiveX Buttons and double clicked on them on design mode to enter their click event?
Have you pasted the code without the Sub headers/definitions?
Have you put "Public sw As Boolean" at the very top of your module?

If you have done so, what kind of problem are you having? Is it not running at all or are the values not being updated at all?

 

[Joneye]

Hello

Have you used ActiveX Buttons and double clicked on them on design mode to enter their click event?
Could be here, ive put a command button in, now i click on properties how would I define the "Click Event"


Have you pasted the code without the Sub headers/definitions?
Copy and pasted as displayed

Have you put "Public sw As Boolean" at the very top of your module?
yes