Macro to check if another macro was activate

[Alwina]

Hi

Is it possible to have a macro to check if another macro was activated? I have the following macro that insert the starting time in a column when you click on the start button.

Sub setStart()
    Call unprotect
    
    If Range("F1") = Empty Or Range("F2") = Empty Then
   ' MsgBox "Cell A5 is empty!"
    Else
    Dim NextRow As Long
    NextRow = GetLastRow("K") + 1

    With Range("K" & NextRow)
        .Value = Now
        .NumberFormat = "yyyy/mm/dd HH:mm:ss"
    End With
    End If
 '  ActiveSheet.CommandButton1.Caption = "STOP"
    Call protect
End Sub

and the following macro to insert the stop time when clicking on the Stop button

Sub setStop()
    Call unprotect
   ' Call Score
     If Range("F1") = Empty Or Range("F2") = Empty Then
    MsgBox "Please Ensure that you clicked on 'Play' before you click on 'Stop'!"
    Else
    Dim NextRow As Long
    NextRow = GetLastRow("L") + 1

    With Range("l" & NextRow)
        .Value = Now
        .NumberFormat = "yyyy/mm/dd HH:mm:ss"
    End With
    calcElapsedTime (NextRow)
'    ActiveSheet.CommandButton1.Caption = "START"
    Call Score
    End If
    Call protect
    
End Sub

Basically i want it before it insert the start time in column K it must check that the last entry in column k must have a value in the same row in column l, if there is no entry in column L in that row it must give an error message "You already pressed Start".

 

[Arithos]

Basicly you want a criteria for the macro to run?

Will the entry(col K) have a specific range each time? You have column K, I and L.
So, something like the below?

[COLOR=#0000ff]'Just assuming last entry(downwards in col K) is in column K then check[/COLOR]
StartCheckRow = range("K:" & rows.count).end(xlup).row
If range("L:" & StartCheckRow) <> "" and range("I:" & StartCheckRow) then
Msxbox "error", vbcritical
end if

 

[Alwina]

HI there Arithos

No, the range will vary.

Thanks for your reply, I tried to run you code but i get a Compile Error - Variable not defined at this line

StartCheckRow =

Should i put it like this?

Sub setStart()
   
    Call unprotect
    
    StartCheckRow = Range("K:" & Rows.count).End(xlUp).Row
    If Range("L:" & StartCheckRow) <> "" Then
    Msxbox "You already clicked on Start!", vbCritical
    End If
    
    If Range("F1") = Empty Or Range("F2") = Empty Then
   ' MsgBox "Cell A5 is empty!"
    Else
    Dim NextRow As Long
    NextRow = GetLastRow("K") + 1

    With Range("K" & NextRow)
        .Value = Now
        .NumberFormat = "yyyy/mm/dd HH:mm:ss"
    End With
    End If
 '  ActiveSheet.CommandButton1.Caption = "STOP"
    Call protect
End Sub

</pre>

 

[Arithos]

Yes, I didnt test it, just typed it in, but I see I added ":" just remove that and it should be okay,
You can change StartCheckRow to LRK ( lastrow K), and "dim LRK as long" should speed it up.

This basicly cheks if the last row in L, has a value, which corresponds with there beeing a value in K, (LRK will alway s have a value)

 

[Alwina]

I have it like this now

Sub setStart()
    Call unprotect
    Dim LastRowK As Long
    
    LastRowK = Range("K" & Rows.Count).End(xlUp).Row
    If Range("L" & LastRowK) <> "" Then
    MsgBox "error", vbCritical
    End If
    
    If Range("F1") = Empty Or Range("F2") = Empty Then
   ' MsgBox "Cell A5 is empty!"
    Else
    Dim NextRow As Long
    NextRow = GetLastRow("K") + 1

    With Range("K" & NextRow)
        .Value = Now
        .NumberFormat = "yyyy/mm/dd HH:mm:ss"
    End With
    'End If
    End If
 '  ActiveSheet.CommandButton1.Caption = "STOP"
    Call protect
End Sub

It seems to work now, however it does not seem to test what i want it to test. Lets say i have the following rows in colum K and L

K................................. L
21/01/2015 11:20..........21/01/2015 11:21
21/01/2015 11:23

If I press the button that activates the SetStart button now, it should give me the error message and stop the macro.

If the Last row in K and L looks like this
K ................................... L
21/01/2015 11:20 ............21/01/2015 11:21

and i press the button that activates SetStart, it should insert the date,time in then next available row in K - Like this
K .................................. L
21/01/2015 11:20 .......... 21/01/2015 11:21
21/01/2015 11:23

Hope it make sense.

 

[Arithos]

Aha!, I misunderstood something =)

Before you run the start again, you want to check if it was stopped last time.
See if this wont do the trick, changed and removed some bits

Sub setStart()    Call unprotect
    Dim NextRow As Long
    
    NextRow = GetLastRow("K") [COLOR=#0000ff]' I just use the same variable name, changes nothing further down. [/COLOR]
    If Range("L" & NextRow) = "" Then
        MsgBox "No Stop value for last runthrough, exiting macro", vbCritical
        exit sub
    End If
        
    If Range("F1") = Empty Or Range("F2") = Empty Then
        msgbox "Fill F1 and F2 then run the macro again." , vbinformation
        exit sub
    Else
    NextRow = GetLastRow("K") + 1
    With Range("K" & NextRow)
        .Value = Now
        .NumberFormat = "yyyy/mm/dd HH:mm:ss"
    End With
    End If
    
    Call protect
End Sub

 

[Alwina]

You are a star! Thanks it is working now!

 

[Alwina]

Just another question.

On my setStop Macro.

Sub setStop()
    Call unprotect
   ' Call Score
    Dim NextRow As Long
            
    If Range("F1") = Empty Or Range("F2") = Empty Then
    MsgBox "Please Ensure that you clicked on 'Play' before you click on 'Stop'!"
    Else
    'Dim NextRow As Long
    NextRow = GetLastRow("L") + 1

    With Range("l" & NextRow)
        .Value = Now
        .NumberFormat = "yyyy/mm/dd HH:mm:ss"
    End With
    calcElapsedTime (NextRow)
'    ActiveSheet.CommandButton1.Caption = "START"
    Call Score
    End If
    Call protect
    
End Sub

If the Last row in K and L looks like this
K ................................... L
21/01/2015 11:20 ............21/01/2015 11:21

and user click on "stop" it should give an error message, "You already stop the game"

How do i change this to fit the above

 NextRow = GetLastRow("K") [COLOR=#0000ff]' I just use the same variable name, changes nothing further down. [/COLOR]
    If Range("L" & NextRow) = "" Then
        MsgBox "No Stop value for last runthrough, exiting macro", vbCritical
        exit sub
    End If

 

[Arithos]

I would think like this,

 If [COLOR=#ff0000]Not[/COLOR] Range("L" & NextRow) = "" Then

Or,

 If Range("L" & NextRow) [COLOR=#ff0000]<>[/COLOR] "" Then

Which does the same thing...