run the macro based on timer counter on userform for specific time

[MKLAQ]

hello

I search for procedure before run the macro . so if I press button to run the macro ,then pops up message " you should wait at this time PM 21:12:00" and if I choose OK then should show timer counter on userform to see how remained time to PM 21:12:00 and if reached for this time , then should run the macro, if it's past then shouldn't work , keep timer counter to see how remained time and if press NO shouldn't show the userform and exit .
thanks

 

[EXCEL MAX]

Let's try this...
Insert userform and name it "UserForm1".
Insert label control in the userform and name it "Label1".
This code goes to the userform module.

Private Sub UserForm_Activate()
 
    While DateValue(vStartTime + TimeValue(vRestTime)) < vEndTime
        DoEvents
        UserForm1.Label1.Caption = Format(vEndTime - Now(), "hh:mm:ss")
        If UserForm1.Label1.Caption = "00:00:00" Then GoTo EX
    Wend
EX: Me.Hide
       MsgBox "Run macro"

End Sub

Insert standard module and this code there.

Option Explicit
Public vMsg, vTime, vFTime
Public vRestTime, vEndTime, vStartTime

Sub PopUpMessage()

    vTime = "21:12:00"
    vFTime = Format(vTime, "AM/PM hh:mm:ss ")
    If Now() > DateValue(Now()) + TimeValue(vTime) Then Exit Sub
    vMsg = MsgBox("You should wait at this time " & _
                 vFTime, vbYesNo, "PopUpMessage")
    If vMsg = 6 And Now() < DateValue(Now()) + TimeValue(vTime) Then
        vEndTime = DateValue(Now()) + TimeValue(vTime)
        vRestTime = Format(Now() - vEndTime, "hh:mm:ss")
        vStartTime = Now()
        UserForm1.Show False
    End If
  
End Sub

 

[MKLAQ]

hi

I dont still test your codes because I want guiding me where I put my macro into macro Sub PopUpMessage()

I have this call col_nn . based on your codes if the time is matched then should run the macro sub col_nn()

 

[EXCEL MAX]

Your macro can be placed in userform module or standard module and
you can call your macro instead show message box "Run macro".

Private Sub UserForm_Activate()
 
    While DateValue(vStartTime + TimeValue(vRestTime)) < vEndTime
        DoEvents
        UserForm1.Label1.Caption = Format(vEndTime - Now(), "hh:mm:ss")
        If UserForm1.Label1.Caption = "00:00:00" Then GoTo EX
    Wend
EX: Me.Hide
'       MsgBox "Run macro"
       Call col_nn

End Sub

Sub col_nn()
    
    MsgBox "This is your macro"
    
End Sub

 

[MKLAQ]

actually I prefer my macro in standard module instead of userform module . I no know if it's right

Sub PopUpMessage()

    vTime = "21:12:00"
    vFTime = Format(vTime, "AM/PM hh:mm:ss ")
    If Now() > DateValue(Now()) + TimeValue(vTime) Then Exit Sub
    vMsg = MsgBox("You should wait at this time " & _
                 vFTime, vbYesNo, "PopUpMessage")
    If vMsg = 6 And Now() < DateValue(Now()) + TimeValue(vTime) Then
        vEndTime = DateValue(Now()) + TimeValue(vTime)
        vRestTime = Format(Now() - vEndTime, "hh:mm:ss")
        vStartTime = Now()
        UserForm1.Show False
    End If
   Call col_nn
End Sub

even if that the macro doesn't work at all

 

[EXCEL MAX]

It's OK to put code in the standard module.
Sometimes it depends what kind of code you need.

 

[MKLAQ]

Sometimes it depends what kind of code you need.

what do you mean ?
it's just random macro to see how works like in your code

Sub col_nn()
    
    MsgBox "This is your macro"
    
End Sub

 

[EXCEL MAX]

When you create subroutine in userform module you can't call it from a standard module,
but subroutine in the standard module you can call from userform module,
from worksheet module or standard module itself.

 

[MKLAQ]

When you create subroutine in userform module you can't call it from a standard module,

but I call my macro from a standard module, then should work !!!

 

[EXCEL MAX]

If I understand, you want to start your macro called "col_nn" placed in the standard module.
Sorry for waiting, It was not easy to understand problem and find right way.
There is no special procedure to do that what you want, so I have create this example.
Hope so I have understand your needs and that you will be able to get something useful.
This goes in the standard module...

Option Explicit
Public vMsg, vTime, vFTime
Public vRestTime, vEndTime, vStartTime
Public vClose As Boolean

Sub col_nn()

    vTime = "21:12:00"
    vFTime = Format(vTime, "AM/PM hh:mm:ss ")
    If Now > DateValue(Now) + TimeValue(vTime) Then
        vClose = True
        Exit Sub
    End If
    vMsg = MsgBox("You should wait at this time " & _
                 vFTime, vbYesNo, "PopUpMessage")
    If vMsg = 6 And Now < DateValue(Now) + TimeValue(vTime) Then
        vEndTime = DateValue(Now) + TimeValue(vTime)
        vRestTime = CDate(Now - vEndTime)
        vStartTime = Now
        UserForm1.Show False
    Else
        vClose = True
    End If
    Do While vClose = False
        DoEvents
    Loop
    UserForm1.Hide
    vClose = False
    
End Sub

Insert userform called "UserForm1" and label called "Label1" in the userform.
Insert this code in the userform module...

Private Sub UserForm_Activate()

    Do While vStartTime + vRestTime < vEndTime
        DoEvents
        UserForm1.Label1.Caption = Format(vEndTime - Now(), "hh:mm:ss")
        If Now > vEndTime Then
            vClose = True
            Exit Do
        End If
    Loop
    
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    vStartTime = vStartTime + vEndTime
    vClose = True
    
End Sub