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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,660
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,660
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,660
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:

Watch MrExcel Video

Forum statistics

Threads
1,101,904
Messages
5,483,646
Members
407,399
Latest member
Rakeforms

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top