Help with running a code

[Dedes]

Hi,

I have made a clock that is running on my sheet3 cell B2.

I wish that when the clock reaches 00:30 a Macro that I have created called give_names runs.

I worte this code on Sheet 3 but doesn't work

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheet3.Range("b2") = "00:30" Then
Call give_names
End If
End Sub


Can anyone help me please?

thanks
Dedes

 

[id107]

Try using Timevalue instead of "00:30"; TimeValue("00:30:00")

 

[Dedes]

Try using Timevalue instead of "00:30"; TimeValue("00:30:00")

Hi

thanks for the help but it doesn't work.

The clock I have made on cell B2 is formated as text. I have tried to make an If formula on a cell and it runs OK.

Formula was: If(B2="00:30",1,0)

This worked fine.

But what I really need is to make a module run after the time hits 00:30.

I have put the code on the Worksheet where the clock is working. I have also tried diferent Private Sub like Worksheet_Activate or Worksheet_SelectionChange but I really don't know witch one to use.

Can you help please?

thanks
Dedes

 

[Dedes]

Hi

I have semi solved the problem because now I am able to call my macro but now I have another issue: the macro that runs the clock stops

So, to make explain my self 100%

I press a button that starts a Macro with a clock

then I made this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If sheet3.Range("b2") = "00:10" Then
Call give_names
End If
End Sub

This works but the clock stops and goes to 00:00.

What can I do to call the give_names at 00:30 but make the clock continue to run?

thanks
Dedes

 

[Dedes]

Hi,

me again,

Sorry for posting so much but I am trying to solve my issue and mean while I discovery something new that may help you guys understand what is happening here.

So, what I have discovered is that while the clock is running if a second Macro on that sheet is asked to run the macro from the clock stops.

How can I avoid this ?

thanks
Dedes

 

[elvgames]

Hi there, i'm not clear on why you built a clock using text but if you type
=NOW() into the cell where your clock is and format it for time 00:00:00 you now have your clock. Then reference this cell in your code like id107 suggested. This should solve your problem. If this is not what you're looking for some reason you should paste your code for the clock, this will help us better understand what you're after.
thanks.
be sure to check out www.elvgames.com !

 

[Dedes]

Hi

This is my code for the clock

Private TimerCell As Range
Private TimerEnabled As Boolean
Private TimerValue As Double

Sub SetTimerCell()
Set TimerCell = Folha3.Range("b2")
TimerCell.NumberFormat = "@"
End Sub

Sub StartTimer()

If TimerEnabled Then Exit Sub
If TimerCell Is Nothing Then SetTimerCell
TimerEnabled = True
ShowTime

End Sub

Sub StopTimer()
TimerEnabled = False
End Sub

Sub ResetTimer()
TimerEnabled = False
If TimerCell Is Nothing Then SetTimerCell
TimerValue = 0 '600 corresponde a 10 minutos'
TimerCell = "00:00" 'aqui diz-se onde se coneça a actualizar o tempo e tirei a forma 00:00.0'
End Sub

Sub ShowTime()

Dim Delay As Single
Dim Mins As Integer
Dim Secs As Variant
Dim StartTime As Single

Delay = 0.1
StartTime = Timer

Do While TimerEnabled


While Timer < StartTime + Delay: Wend

TimerValue = TimerValue + 0.1 'aqui muda-se para andar para a frente ou para trás'
Mins = Int(TimerValue) \ 60
Secs = TimerValue - (Mins * 60)
TimerCell.Value = Format(Mins, "00") & ":" & Format(Secs, "00") 'aqui tem tirei o 00.0'

StartTime = Timer


DoEvents

Loop



End Sub

This is the code running on my sheet3 - Folha3 in Portuguese.

Then I have the code Ive already posted to start the macro give_names.

I have tried several things but happens the same to them all: If a second Macro is asked to run it stops the previous one from running.

thanks
Dedes

 

[elvgames]

I think i'm starting to understand a little better. i ran into a similar problem with my solitaire game. copy and paste the below code exactly into a blank workbook and run it for 40 seconds. let me know if this puts you on the right track. the only problem this poses is if you begin the macro before midnight and it continues beyond midnight. if this code works for you i can show you a work around for that.

Sub a()
'
' a Macro
'
If Range("c1").Value = "stop" Then ' type this 'safe' word into cell c1 if you want the macro to end
End
End If
If Range("b1").Value = 1 Then ' if you end the macro reset cell b1 to blank or 0 before you start it again
GoTo 5
End If
Range("B3").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*86400)/10"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN(RC[-1],0)"
Range("A1:A3").Select
Selection.NumberFormat = "h:mm:ss;@"
Range("A7").Select
Selection.NumberFormat = "General"
Range("B1").Select
Selection.NumberFormat = "0"
Range("B3").Select
Selection.NumberFormat = "General"
Range("C3").Select
Selection.NumberFormat = "General"
Range("A1:B2").Select
Selection.Font.ThemeColor = xlThemeColorDark1
Range("B3:C3").Select
Selection.Font.ThemeColor = xlThemeColorDark1
Range("c1") = "Type the word 'stop' in cell c1 to stop the macro"
Range("C1").Select
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Range("a6") = "Number of times macro get_names ran:"
Range("a1").Value = Now()
Range("a2").Value = Range("a1").Value ' this becomes the time stamp for beginning of macro
Range("b1").Value = 1 ' let's the macro know it already set the time stamp
5
Application.OnTime Now + TimeValue("00:00:1"), "a"
' this line will make the macro run automatically every 1 second. to make it stop hit esc repeatedly or type the
' word 'stop' into cell c1, this is going to allow you to use the spreadsheet while the macro is performing
' in the background
Range("a3").Value = Now() - Range("a2").Value ' this is your new time clock, no matter how slow the macro runs it will
' always 'catch up' because it will always be looking at real time minus your original time stamp

If Range("b3").Value = Range("c3").Value And Range("b3").Value > 0 Then ' checks if timer is at a multiple of 10
Range("a7").Value = Range("a7").Value + 1 ' this line would be replaced with your 'get_names' macro
End If



'
End Sub

 

[Dedes]

I think i'm starting to understand a little better. i ran into a similar problem with my solitaire game. copy and paste the below code exactly into a blank workbook and run it for 40 seconds. let me know if this puts you on the right track. the only problem this poses is if you begin the macro before midnight and it continues beyond midnight. if this code works for you i can show you a work around for that.

Sub a()
'
' a Macro
'
If Range("c1").Value = "stop" Then ' type this 'safe' word into cell c1 if you want the macro to end
End
End If
If Range("b1").Value = 1 Then ' if you end the macro reset cell b1 to blank or 0 before you start it again
GoTo 5
End If
Range("B3").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*86400)/10"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=ROUNDDOWN(RC[-1],0)"
Range("A1:A3").Select
Selection.NumberFormat = "h:mm:ss;@"
Range("A7").Select
Selection.NumberFormat = "General"
Range("B1").Select
Selection.NumberFormat = "0"
Range("B3").Select
Selection.NumberFormat = "General"
Range("C3").Select
Selection.NumberFormat = "General"
Range("A1:B2").Select
Selection.Font.ThemeColor = xlThemeColorDark1
Range("B3:C3").Select
Selection.Font.ThemeColor = xlThemeColorDark1
Range("c1") = "Type the word 'stop' in cell c1 to stop the macro"
Range("C1").Select
Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
Range("a6") = "Number of times macro get_names ran:"
Range("a1").Value = Now()
Range("a2").Value = Range("a1").Value ' this becomes the time stamp for beginning of macro
Range("b1").Value = 1 ' let's the macro know it already set the time stamp
5
Application.OnTime Now + TimeValue("00:00:1"), "a"
' this line will make the macro run automatically every 1 second. to make it stop hit esc repeatedly or type the
' word 'stop' into cell c1, this is going to allow you to use the spreadsheet while the macro is performing
' in the background
Range("a3").Value = Now() - Range("a2").Value ' this is your new time clock, no matter how slow the macro runs it will
' always 'catch up' because it will always be looking at real time minus your original time stamp

If Range("b3").Value = Range("c3").Value And Range("b3").Value > 0 Then ' checks if timer is at a multiple of 10
Range("a7").Value = Range("a7").Value + 1 ' this line would be replaced with your 'get_names' macro
End If



'
End Sub

Hi

I was a few days out. I will try the code and see how it goes.

thanks
Dedes