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

welcome to the forum.

did you try deleting .exd files?
https://social.technet.microsoft.com...eitproprevious

Quote:

---

Originally Posted by mancubus

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.

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.

Thank you for helping me.

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

Code:

---

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

1. Don't use any protection
2. test this code

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

---

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

Quote:

---

Originally Posted by snb

1. Don't use any protection
2. test this code

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.

Quote:

---

Originally Posted by Jan Karel Pieterse

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

thank you

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.

Quote:

---

Originally Posted by Jan Karel Pieterse

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?

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


Attachment 27518


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

Attachment 27519

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

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

Quote:

---

Originally Posted by Jan Karel Pieterse

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

---

Attachment 27520

True :doh:, but they still look wrong to me :think:

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'

Quote:

---

Originally Posted by Paul_Hossler

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


Attachment 27518


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

Attachment 27519

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

JKP did all the work

Quote:

---

Originally Posted by Paul_Hossler

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

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)

Quote:

---

Originally Posted by Jan Karel Pieterse

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