Workbook should not open - code require
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Workbook should not open - code require
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Workbook should not open - code require

    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..

  2. #2
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    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 by sykes; Jul 19th, 2019 at 07:55 AM.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    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

  4. #4
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    Quote Originally Posted by VBABEGINER View Post
    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 by Steve_; Jul 19th, 2019 at 09:24 AM.

  5. #5
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    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 by sykes; Jul 19th, 2019 at 09:40 AM.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  6. #6
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    Hi Sykes,
    Thank You for your valuable time and solution.

    It works..

    Quote Originally Posted by sykes View Post
    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.

  7. #7
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    Hi Steve, Thank You for your valuable time and reply. But what is the password in this case..

    Quote Originally Posted by Steve_ View Post
    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 by VBABEGINER; Jul 19th, 2019 at 12:01 PM.

  8. #8
    Board Regular
    Join Date
    Jun 2011
    Location
    Mumbai, Maharashtra,India.
    Posts
    1,092
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    Hi Steve.. pls give me solution about password.. my entire work in that file.. it is not opening now..

    Quote Originally Posted by Steve_ View Post
    Code:
    End Sub

  9. #9
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,560
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    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.
    Sykes
    Windows 10 / XL 2016
    Members don't have crystal balls, so PLEASE describe your situation, code,
    and requirements ACCURATELY in your first post. It saves hair!
    Better still - upload your spreadsheet (and formulae!) with this great bit of kit.


    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook should not open - code require

    Quote Originally Posted by VBABEGINER View Post
    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 by Steve_; Jul 19th, 2019 at 12:43 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •