Mouse click VBA

[bigdaddy00]

I have this code to click between two cells back and forth every minute for an hour but i'm trying to make it into two buttons so i can have it run continuously and click a stop button when needed. This code i've pasted gives the blue wheel and doesn't allow me to click anything with excel. Help would be much appreciated, thank you.

Range("A1").Select

Application.Wait (Now + TimeValue("00:01:00"))

For a = 1 To 60

ActiveCell.Offset(1, 0).Select

Application.Wait (Now + TimeValue("00:01:00"))

ActiveCell.Offset(-1, 0).Select

Application.Wait (Now + TimeValue("00:01:00"))

Next a

End Sub

 

[6StringJazzer]

The strategy is to set a timer instead of doing a Wait. But what you mean by "make it into two buttons"?

 

[Micron]

Agreed. Plus, 1 button would be enough. If button caption is Start Process at the beginning, do whatever and change the caption to End Process. If when clicked the caption is End Process, then end it.

 

[bigdaddy00]

The strategy is to set a timer instead of doing a Wait. But what you mean by "make it into two buttons"?

Do you have a solution in mind?

 

[6StringJazzer]

I have a solution in mind but I can't articulate a solution specifically for you unless I know what you mean by "make it into two buttons."

Here is a generic example of utility subs that allow you to set a timer and stop it. This code must be in a standard module (e.g., Module1).

Dim RunWhen As Double ' time that timer is set to expire
Const TimerIntervalSeconds As Double = 0.2 ' the amount of time in seconds to wait from
                                           ' when the timer is set to when it expires
Dim R As Long


Public Sub StartTimer()
   
   SetNextTimer
   Sheet1.Cells.ClearContents
   R = 1
    
End Sub

Public Sub SetNextTimer()

   ' Convert seconds to fraction of a day
   RunWhen = Now + TimerIntervalSeconds / (CLng(24) * CLng(60) * CLng(60))
   Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="TimedAction", _
      Schedule:=True

End Sub


Public Sub StopTimer()

    On Error Resume Next
    
    Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="TimedAction", _
      Schedule:=False

End Sub

' You can name this anything you want. Update the call to OnTime above to match.
Public Sub TimedAction()

   Static count As Long
   
   SetNextTimer
   
   Sheet1.Cells(R, "A") = R
   Sheet1.Cells(R, "B") = Time
   R = R + 1
   
   
End Sub

 

[Micron]

Not tried by me, but perhaps

VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

I have a need to run a piece of code every 120 seconds. I am looking for an easy way to do this in VBA. I know that it would be possible to get the timer value from the Auto_Open event to prevent

stackoverflow.com

 

[bigdaddy00]

Hi there

I have a solution in mind but I can't articulate a solution specifically for you unless I know what you mean by "make it into two buttons."

Here is a generic example of utility subs that allow you to set a timer and stop it. This code must be in a standard module (e.g., Module1).

Dim RunWhen As Double ' time that timer is set to expire
Const TimerIntervalSeconds As Double = 0.2 ' the amount of time in seconds to wait from
                                           ' when the timer is set to when it expires
Dim R As Long


Public Sub StartTimer()
  
   SetNextTimer
   Sheet1.Cells.ClearContents
   R = 1
   
End Sub

Public Sub SetNextTimer()

   ' Convert seconds to fraction of a day
   RunWhen = Now + TimerIntervalSeconds / (CLng(24) * CLng(60) * CLng(60))
   Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="TimedAction", _
      Schedule:=True

End Sub


Public Sub StopTimer()

    On Error Resume Next
   
    Application.OnTime _
      EarliestTime:=RunWhen, _
      Procedure:="TimedAction", _
      Schedule:=False

End Sub

' You can name this anything you want. Update the call to OnTime above to match.
Public Sub TimedAction()

   Static count As Long
  
   SetNextTimer
  
   Sheet1.Cells(R, "A") = R
   Sheet1.Cells(R, "B") = Time
   R = R + 1
  
  
End Sub

I think one button would suffice as long as i'm able to stop it without using the task manager. Two buttons i used to have a tool i lost where the start button would start the clicks between two cells and the stop button would stop the macro from running, by button i mean the activex control button.