VBA to prevent workbook from opening at a set time of day

Dave8899

New Member
Joined
Jan 17, 2019
Messages
14
Hi all I’m looking for a way to prevent people from opening a workbook between 2-2:30pm every day. So it can be updated on the server.

I have set a Application.OnTime vba to close at a set time 1:59 and then repeated it every min until 2:29.

But people are still able to open the work book between 2/2:30 they kicked out after 1min but that has already stopped the update.

Dose anyone know of a way to prevent the workbook from opening between 2/2:30.

Thanks in advance
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
956
Hi Dave,

In addition to your code to kick people out of the sheet, you can add a Workbook_Open() event.

ALT+F11 > Double Click 'This Workbook' > and paste in the following code:
Code:
Private Sub Workbook_Open()
    If Time >= TimeValue("14:00:00") And Time <= TimeValue("14:30:00") Then
        ThisWorkbook.Close SaveChanges:=False
    End If
End Sub

Cheers
Caleeco
 

Watch MrExcel Video

Forum statistics

Threads
1,109,013
Messages
5,526,266
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top