Macro to prevent macros from running until certain time

1nk3d

Board Regular
Joined
May 31, 2016
Messages
51
Longtime lurker, usually can find what I need but this is bothering me.....

I have a report for work, that users pull data from. They click a button for the day of the week, and it pulls data from a report I upload, and gives them data for the previous day. However, many people are pulling it before I have the data (Usually 8:00). I want a macro to add in to my existing macro where if it is before 8:00, it will not allow them to run it.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Here is a portion. I have a few others where it in essence pulls the data and pastes to other sheets.


Code:
Option Explicit
        Public Sub OpenPrevWeek()
 
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet
 
Set wkbMyWorkbook = ActiveWorkbook
 
Workbooks.Open ("LINK TO REPORT")
 
 
Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet
 
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "Data"
 
wkbMyWorkbook.Activate
wkbWebWorkbook.Close
PW
 
End Sub
 
Sub PW()
'
'
 
'
    Sheets("Data").Select
    Sheets("output").Visible = True
    Sheets("Data").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Range("A3:BE7184").Select
    Selection.Copy
    Sheets("output").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("output").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Data").Select
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Application.Goto Sheets("Fri").Range("A1"), True
    Openhoc
End Sub
 
Upvote 0
Call the relevant code from the code Mike suggested.
Code:
Sub mySub()
    If Hour(Now()) < 8 Then
        MsgBox "too early"
    Else
        OpenPrevWeek
    End If

End Sub
 
Upvote 0
Easy enough, appreciate all the help. What if I wanted to change the time to 8:30? I struggle with time in excel.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top