VBA MsgBox question

[Pinaceous]

Hi All,

How do you run a timed pop up to pop up on certain opened tabs of your workbook?

For example, if I'm running

YourSub1

to execute at ie. 12:00pm I want it exclusive for sheet 1 and no other tabs.

Thank you all in advance!

R/
Pinaceous

 

[Logit]

Not certain what you mean by

exclusive for sheet1

This code (paste into ThisWorkbook module) will initiate the "timer" when the workbook is first opened:

Private Sub Workbook_Open()


    Application.OnTime TimeValue("12:00:00"), "MyMacro"   '<-- create another macro in a Routine Module called MyMacro
    
     'in the MyMacro you'll create code for the MsgBox.


End Sub

Sub MyMacro()
     MsgBox"This is your message"
End Sub

 

[Pinaceous]

Hello Logit,

Many thanks for your reply!

Can the time be adjusted for the

MyMacro

code to work only between for example Midnight and 12PM?

For example;

Application.OnTime TimeValue("00:00:01"), "MyMacro"

to

Application.OnTime TimeValue("12:00:00"), "MyMacro

??

Thank you!
Pinaceous

 

[Pinaceous]

Would this work ??

Application.OnTime TimeValue("0:00:01"), , TimeValue("12:00:00"), "MyMacro"

 

[Logit]

.
I believe your questions are leading to a larger goal.

Please describe what it is you are ultimately attempting to do. This will avoid going back and forth and get to the heart of the matter.

 

[Pinaceous]

Hi Logit,

Your very perceptive. What I'm trying to do is provide for a button to execute a specific macro where when clicked it will execute one macro1 only between midnight to 12pm and a different macro2 from 12:01pm to 23:59.

I was reading up on this sort of application and would like it for what I'm working on.

Thanks,
Pinaceous

 

[Logit]

.
This is one method to accomplish your goal :

Paste into a Routine Module:

Option Explicit


Public Function InRange(startTime As Date, endTime As Date) As Boolean


    Dim dtNow As Date


    dtNow = Now


    Select Case TimeValue(dtNow)
        Case Is > TimeValue(startTime)
            If TimeValue(endTime) > TimeValue(dtNow) Then
                InRange = True
            End If
        Case Is > TimeValue(startTime)
            If TimeValue(endTime) > TimeValue(dtNow) Then
                InRange = True
            Else
                InRange = False
            End If
        Case Is < TimeValue(startTime) And (TimeValue(endTime) > TimeValue(dtNow))
                InRange = True
        Case Else
            InRange = False
    End Select


End Function


Sub WatTyme()
'If the time is between midnight and noon, the first macro will be run - otherwise any other time, the second macro is run.


    If InRange(CDate("00:00:01"), CDate("12:00:00")) Then
        Macro1
    Else
        Macro2
    End If


End Sub

 

[Pinaceous]

Logit!!!!!!!!!

That worked out fantastic!!

Many thanks for posting that code!

-Pinaceous

 

[Logit]

.
You are most welcome. Thanks.