Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Can’t find project or library - Microsoft Forms 2.0 ticked in error?

  1. #1
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location

    Can’t find project or library - Microsoft Forms 2.0 ticked in error?

    Hi All

    i really hope someone can help with this - it’s becoming a massive pain!

    At work we use spreadsheets to record work that has been completed. There is a simple macro to hide some of the sheets when the workbook is opened. These spreadsheets worked fine for years until we upgraded to windows 10. Now, upon opening, an error is displayed intermittently. “Can’t find project or library” - either that or the spreadsheet immediately closes when opened. I have checked the references and none are marked as MISSING. what I did notice was that Microsoft Forms 2.0 was ticked but there is no user form within the spreadsheets. When I tried to untick it, it won’t allow it and says the reference is in use. As a test I added a user form to all of the spreadsheets and am now waiting to see whether any users report the error.

    We were told by our tech team that the error was being caused because the document was large, but I thought that this sounded odd and the error has now occurred on some much smaller docs. Upon checking they also have the user form reference ticked when there is no user form.

    I have found instructions to fix the docs when this occurs, but it’s getting time consuming. It’s a possibility that by adding a user form I won’t have to do it anymore, but wondered whether anyone could shed any light on it? Could the error be caused by the User Form ref being ticked in error? Also, why is it getting ticked in the first place?

    Any help would be gratefully received

    thanks x

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    welcome to the forum.

    did you try deleting .exd files?
    https://social.technet.microsoft.com...eitproprevious
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by mancubus View Post
    welcome to the forum.

    did you try deleting .exd files?
    https://social.technet.microsoft.com...eitproprevious
    there don’t appear to be any. Although our systems are heavily restricted with what we can access.

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You could assume the VBA-code is referring to that library without you being aware of it,
    We can only tell if you show us the code the file is using.

  5. #5
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Thank you for helping me.

    I am a novice so the code may not be as efficient s it could be....

    Private Sub Workbook_Open()
    
    ActiveWorkbook.Unprotect "12345"
    Worksheets("default").Visible = xlSheetVisible
    'hide everything
    'Warbreck
    Worksheets("Start").Visible = xlSheetVeryHidden
    Worksheets("End").Visible = xlSheetVeryHidden
    
    Worksheets("AA").Visible = xlSheetVeryHidden
    Worksheets("BB").Visible = xlSheetVeryHidden
    Worksheets("CC").Visible = xlSheetVeryHidden
    Worksheets("DD").Visible = xlSheetVeryHidden
    Worksheets("EE").Visible = xlSheetVeryHidden
    Worksheets("FF").Visible = xlSheetVeryHidden
    Worksheets("GG").Visible = xlSheetVeryHidden
    Worksheets("Summary1").Visible = xlSheetVeryHidden
    Worksheets("Summary2").Visible = xlSheetVeryHidden
    Worksheets("Summary3").Visible = xlSheetVeryHidden
    Worksheets("Summary4").Visible = xlSheetVeryHidden
    
    Select Case Environ("UserName")
      
        
    '
      Case "11111111"
        Worksheets("AA").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
        
     '
      Case "22222222"
        Worksheets("BB").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
        
    '
      Case "33333333"
        Worksheets("CC").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
        
    '
      Case "44444444"
        Worksheets("DD").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
        
        
    '
      Case "5555555"
        Worksheets("EE").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
    '
      Case "66666666"
        Worksheets("FF").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
    
        
    '
      Case "7777777"
        Worksheets("GG").Visible = xlSheetVisible
        Worksheets("default").Visible = xlSheetVeryHidden
        
        
        
    '-----------------------
    'STAFF WITH ACCESS OT SEE ALL TABS
     
    'JOE
      Case "94800000"
    Worksheets("AA").Visible = xlSheetVisible
    Worksheets("BB").Visible = xlSheetVisible
    Worksheets("CC").Visible = xlSheetVisible
    Worksheets("DD").Visible = xlSheetVisible
    Worksheets("EE").Visible = xlSheetVisible
    Worksheets("FF").Visible = xlSheetVisible
    Worksheets("GG").Visible = xlSheetVisible
    Worksheets("Summary1").Visible = xlSheetVisible
    Worksheets("Summary2").Visible = xlSheetVisible
    Worksheets("Summary3").Visible = xlSheetVisible
    Worksheets("Summary4").Visible = xlSheetVisible
        
    Worksheets("default").Visible = xlSheetVeryHidden
    Worksheets("Start").Visible = xlSheetVeryHidden
    Worksheets("End").Visible = xlSheetVeryHidden
     
    'ADAM
      Case "095993002"
    Worksheets("AA").Visible = xlSheetVisible
    Worksheets("BB").Visible = xlSheetVisible
    Worksheets("CC").Visible = xlSheetVisible
    Worksheets("DD").Visible = xlSheetVisible
    Worksheets("EE").Visible = xlSheetVisible
    Worksheets("FF").Visible = xlSheetVisible
    Worksheets("GG").Visible = xlSheetVisible
    Worksheets("Summary1").Visible = xlSheetVisible
    Worksheets("Summary2").Visible = xlSheetVisible
    Worksheets("Summary3").Visible = xlSheetVisible
    Worksheets("Summary4").Visible = xlSheetVisible
        
    Worksheets("default").Visible = xlSheetVeryHidden
    Worksheets("Start").Visible = xlSheetVeryHidden
    Worksheets("End").Visible = xlSheetVeryHidden
     
     
    End Select
    ActiveWorkbook.Protect "12345"
    End Sub
    Private Sub workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Unprotect "12345"
    Worksheets("default").Visible = xlSheetVisible
    Worksheets("AA").Visible = xlSheetVeryHidden
    Worksheets("BB").Visible = xlSheetVeryHidden
    Worksheets("CC").Visible = xlSheetVeryHidden
    Worksheets("DD").Visible = xlSheetVeryHidden
    Worksheets("EE").Visible = xlSheetVeryHidden
    Worksheets("FF").Visible = xlSheetVeryHidden
    Worksheets("GG").Visible = xlSheetVeryHidden
    Worksheets("Summary1").Visible = xlSheetVeryHidden
    Worksheets("Summary2").Visible = xlSheetVeryHidden
    Worksheets("Summary3").Visible = xlSheetVeryHidden
    Worksheets("Summary4").Visible = xlSheetVeryHidden
    Worksheets("Start").Visible = xlSheetVeryHidden
    Worksheets("End").Visible = xlSheetVeryHidden
        
        
    Worksheets("default").Activate
        
    ActiveWorkbook.Protect "12345"
        
    
        End Sub
    I have amended the code to take out personal info etc

    thanks again x

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    1. Don't use any protection
    2. test this code

    Private Sub Workbook_Open()
        Sheets("Default").Visible = -1
        For Each it In Sheets
           it.Visible = it.Name = "Default"
        Next
        
        Sheets("AA").Visible = Environ("UserName") = "1111"
        Sheets("BB").Visible = Environ("UserName") = "2222"
        Sheets("CC").Visible = Environ("UserName") = "3333"
        Sheets("DD").Visible = Environ("UserName") = "4444"
        Sheets("EE").Visible = Environ("UserName") = "555"
        Sheets("FF").Visible = Environ("UserName") = "6666"
        Sheets("GG").Visible = Environ("UserName") = "7777"
    
       Select Case Environ("username")
        Case "94800000", "095993002"
          For Each it In Sheets
            it.Visible = -1
          Next
        End Select
        Sheets("default").Visible = 2
    End Sub

  7. #7
    Can you upload your file? You can clear all cells on all sheets (and edit the ThisWorkbook code so there are no names in there).
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  8. #8
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by snb View Post
    1. Don't use any protection
    2. test this code

    Private Sub Workbook_Open()
        Sheets("Default").Visible = -1
        For Each it In Sheets
           it.Visible = it.Name = "Default"
        Next
        
        Sheets("AA").Visible = Environ("UserName") = "1111"
        Sheets("BB").Visible = Environ("UserName") = "2222"
        Sheets("CC").Visible = Environ("UserName") = "3333"
        Sheets("DD").Visible = Environ("UserName") = "4444"
        Sheets("EE").Visible = Environ("UserName") = "555"
        Sheets("FF").Visible = Environ("UserName") = "6666"
        Sheets("GG").Visible = Environ("UserName") = "7777"
    
       Select Case Environ("username")
        Case "94800000", "095993002"
          For Each it In Sheets
            it.Visible = -1
          Next
        End Select
        Sheets("default").Visible = 2
    End Sub
    Thank you for responding. It's amazing how you have managed to condense the code so much! Mine was so long winded!

    Does having protection on cause issues? Should this shorter code get rid of the can't find project/library error?

    Also, is there a way of displaying a message if the persons user id does not match? Something to say that they don't have access? At the moment it is displaying a run time error if the user id does not appear in the code.
    Last edited by Weaholt; 12-01-2020 at 06:18 AM.

  9. #9
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by Jan Karel Pieterse View Post
    Can you upload your file? You can clear all cells on all sheets (and edit the ThisWorkbook code so there are no names in there).
    Thank you-I will amen the code ad upload

  10. #10
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    thank you
    Attached Files Attached Files

  11. #11
    I have taken these steps:
    - Open VBA editor
    - Copy code in ThisWorkbook (this should be repeated for any module contaiing code)
    - Close VBA Editor
    - Save-as file to xlsx format
    - Close Excel
    - Open Excel
    - Open xlsx file
    - Open VBA editor
    - Paste back code to ThisWorkbook (and any other modules!)

    Reference to Forms library is gone.
    Attached Files Attached Files
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  12. #12
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by Jan Karel Pieterse View Post
    I have taken these steps:
    - Open VBA editor
    - Copy code in ThisWorkbook (this should be repeated for any module contaiing code)
    - Close VBA Editor
    - Save-as file to xlsx format
    - Close Excel
    - Open Excel
    - Open xlsx file
    - Open VBA editor
    - Paste back code to ThisWorkbook (and any other modules!)

    Reference to Forms library is gone.
    Thank you for your help. Is this what could have been causing the error?

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    My 2 cents - I think somehow your WB got corrupted

    After I unprotected the WB and Sheet2 and deleted all rows and columns, there was still a lot of orphaned data. They should have been close to 0


    Capture.JPG


    Also there was a left over Sheet that was probably corrupted (my guess is that was the issue)

    Capture2.JPG

    I also noticed in the XML that you have a LOT of defined names with missing references, but they didn't show in the WB side to be deleted


    I think JKP was correct, and the only way out is to rebuild in a new WB
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    The range names are hidden names controlled by custom views, there are 76 custom views in the workbook.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by Jan Karel Pieterse View Post
    The range names are hidden names controlled by custom views, there are 76 custom views in the workbook.

    Capture.JPG

    True , but they still look wrong to me

    Maybe CV's are like that -- just looks funny. Don't know if that was the issue, but I still think it was that Sheet14 'left over'
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by Paul_Hossler View Post
    My 2 cents - I think somehow your WB got corrupted

    After I unprotected the WB and Sheet2 and deleted all rows and columns, there was still a lot of orphaned data. They should have been close to 0


    Capture.JPG


    Also there was a left over Sheet that was probably corrupted (my guess is that was the issue)

    Capture2.JPG

    I also noticed in the XML that you have a LOT of defined names with missing references, but they didn't show in the WB side to be deleted


    I think JKP was correct, and the only way out is to rebuild in a new WB
    Thank you - and thank you JKP too.

    x

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    JKP did all the work
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by Paul_Hossler View Post
    JKP did all the work
    HI - after following your advice, it is still happening. Where I work, the "experts" have said that it's due to Excel 2016 being unstable and it can't cope with multiple users accessing the same workbook. For info -the workbook isn't shared, the users access at different times.
    The workbook always worked without any problems when we used Excel 2010, it is since we were upgraded that the problems started. I can’t help but feel that I am being fobbedoff because they don’t actually know what is causing the problem. How can amore up to date version of an application be worse that the previous version?

    Any thoughts?

    thanks


  19. #19
    In what type of location is this workbook stored? (OneDrive, Sharepoint, shared network drive, ...)
    Are all users using the same version of Excel?
    Is your current version of the file "clean"? (I'm happy to take another look if you either upload here or send it to me)
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  20. #20
    VBAX Regular
    Joined
    Nov 2020
    Posts
    11
    Location
    Quote Originally Posted by Jan Karel Pieterse View Post
    In what type of location is this workbook stored? (OneDrive, Sharepoint, shared network drive, ...)
    Are all users using the same version of Excel?
    Is your current version of the file "clean"? (I'm happy to take another look if you either upload here or send it to me)
    HI - it's on a shared network. I have similar workbooks saved which are only accessed by me and they have done it as well now. Yes, all users are using the same version of excel. This was a concern so I entered some code which recorded the version of excel used on the last save and they were all the same.

    When you ask if the current version of the file is clean, what do you mean?

    PS - when the error is displayed, at the moment, I am making a copy of the workbook,opening it without enabling macros, going into references and just adding a tick. This then allows the workbook to function normally for a while.

    thanks for your help
    Last edited by Weaholt; 12-14-2020 at 06:10 AM.

Posting Permissions

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