Workbook should not open - code require

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
Hi All Board members,

Can any one please provide me code..

the excel file which i will share today with someone..should not open that workbook without password..

Pls assist..
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,743
Office Version
365
Platform
Windows
Use the search facility - there shouldn't be any need to post for this.
There are hundreds of similar posts here, about this subject - just do a search for "Password protect workbook" or similar.
 
Last edited:

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
Hi Sykes.. Appreciate your for reply. But i did extensive search before to this thread. And i have specific requirement like, suppose today workbook file which i have given someone else, then that workbook should not be open after today's date.

Like, If mydate > system date then
Ask for password
else
if mydate = Today() then
open
end if
end if
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
Hi Sykes.. Appreciate your for reply. But i did extensive search before to this thread. And i have specific requirement like, suppose today workbook file which i have given someone else, then that workbook should not be open after today's date.

Like, If mydate > system date then
Ask for password
else
if mydate = Today() then
open
end if
end if

Code:
Private Sub Workbook_Open()
    myDate = "7/19/2019 8:21:22 AM"
    doOpen = False: aFail = 0
    If myDate > Now Then
        Do Until doOpen
            uPwd = InputBox("Enter Password", "Authentication Required", Environ("USERNAME"))
            'Here, you would need code to check the users input against the correct password
            Select Case doOpen
                Case True
                    Exit Sub
                Case Else
                    aFail = aFail + 1
                    If aFail >= 3 Then
                        Application.DisplayAlerts = False
                        ThisWorkbook.Close
                    End If
            End Select
        Loop
    End If
End Sub
 
Last edited:

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,743
Office Version
365
Platform
Windows
Code:
Private Sub Workbook_Open()
If Sheets("Sheet1").Range("A1").Value = Date Then Exit Sub
If InputBox("Password please", "Password check") <> "Password" Then Me.Close (False)

End Sub
You'll need to change the sheet / range references, for those of your own, and put today's date into the cell. This is to save having to hard-code the date into your VBA each time you re-distribute the workbook. You could also have some code in the workbook_open event, to put today's date into the cell, to save you having to do that each time.
Not very secure, though, as users can just open workbook with macro security set to "High" i.e. don't allow macros to run, then open the workbook anyway.
Also, remember anyone who opens the VBA browser, can see the password hard-coded into your code, so better to lock down the project properties with a different password - in the VBA browser.
 
Last edited:

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
Hi Sykes,
Thank You for your valuable time and solution.

It works..

Code:
Private Sub Workbook_Open()
If Sheets("Sheet1").Range("A1").Value = Date Then Exit Sub
If InputBox("Password please", "Password check") <> "Password" Then Me.Close (False)

End Sub
You'll need to change the sheet / range references, for those of ..........VBA browser.
 

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,130
Hi Steve, Thank You for your valuable time and reply. But what is the password in this case..

Code:
Private Sub Workbook_Open()
    myDate = "7/19/2019 8:21:22 AM"
    doOpen = False: aFail = 0
    If myDate > Now Then
        Do Until doOpen
            uPwd = InputBox("Enter Password", "Authentication Required", Environ("USERNAME"))
            'Here, you would need code to check the users input against the correct password
            Select Case doOpen
                Case True
                    Exit Sub
                Case Else
                    aFail = aFail + 1
                    If aFail >= 3 Then
                        Application.DisplayAlerts = False
                        ThisWorkbook.Close
                    End If
            End Select
        Loop
    End If
End Sub
now my file is not opening..
 
Last edited:

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,743
Office Version
365
Platform
Windows
ALWAYS test on a copy of your treasured work, FIRST!

In case Steve's off line for a while.....
Can you open your Excel application, go into macro security, and set it to disable all macros, or at least "Ask before opening" then try and open the workbook without the macros running? This should allow you to access the code, and disable the problem.
 

Steve_

Board Regular
Joined
Apr 28, 2010
Messages
167
Hi Steve.. pls give me solution about password.. my entire work in that file.. it is not opening now..

The password should simply be blank unless you set it to something.

Either way, when it prompts you for the password, hit ctrl+break.

My guess here is that you either did not set a password or you did not write code to set doOpen to true when the correct password is provided
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,963
Messages
5,508,417
Members
408,684
Latest member
Amos101

This Week's Hot Topics

Top