Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Macro to prevent macros from running until certain time

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to prevent macros from running until certain time

    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.

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,703
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    Code:
    Sub mySub()
        If Hour(Now()) < 8 Then
            MsgBox "too early"
        Else
            ' current code
        End If
    End Sub

  3. #3
    New Member
    Join Date
    May 2016
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    Compile error: Expected end sub

    I put my current macro chain after else?





    Quote Originally Posted by mikerickson View Post
    Code:
    Sub mySub()
        If Hour(Now()) < 8 Then
            MsgBox "too early"
        Else
            ' current code
        End If
    End Sub

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    12,569
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    Quote Originally Posted by 1nk3d View Post
    Compile error: Expected end sub

    I put my current macro chain after else?
    Show us your code.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    New Member
    Join Date
    May 2016
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    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

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,692
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    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
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    May 2016
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

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

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,703
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    Code:
    If (Now() - Int(Now()) < TimeValue("8:30:00") Then

  9. #9
    New Member
    Join Date
    May 2016
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    I am getting "Compile error: syntax error when I use this code, the above one worked fine...

    Quote Originally Posted by mikerickson View Post
    Code:
    If (Now() - Int(Now()) < TimeValue("8:30:00") Then

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,703
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Macro to prevent macros from running until certain time

    I missed a parenthesis

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •