Macro button to work between specific times and dates

[Pauljj]

Hi All

I want a macro which is currently controlled by a button, to only work on specific dates throughout the year, ie

3 and 4 June
6 and 7 July
21 and 22 August


and so on, combined with that, I need it to work from 1pm on the first day to 6 pm on the last..........so 1pm on the 3rd June to 6pm on the 4th June etc etc

Is there a practical way to go about this.

I had throught about setting up a table in excel with the dates and times then having the macro check if the computer time fits with any of those dates/times

Does anyone have any bright ideas ?

 

[steve the fish]

If you create a date table in 'Sheet1' cells 'A1:B4' then this sort of thing will work. Dates in the table must be true dates not text.

Sub aaaa()

Dim myDay As Double
Dim mySheet As Worksheet
Dim myNum As Integer

myDay = Int(Now)
Set mySheet = Sheets("Sheet1") 'set sheet as correct sheet name of date table

myNum = WorksheetFunction.CountIfs(mySheet.Range("A1:A100"), "<=" & myDay, mySheet.Range("B1:B100"), ">=" & myDay)

If myNum = 0 Then 'exit if dates not in range
    MsgBox "Dates not in range. Macro exits"
    Exit Sub
End If

MsgBox "Macro Continues" 'continue macro if dates ok

End Sub

 

[Pauljj]

Perfect, thank you very much, works a treat