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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,696
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,696
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,696
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,102,761
Messages
5,488,687
Members
407,651
Latest member
Halosty

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top