Workbook should not open - code require

VBABEGINER

Well-known Member
Joined
Jun 15, 2011
Messages
1,284
Office Version
  1. 365
Platform
  1. Windows
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..
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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