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

[Weaholt]

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

[mancubus]

welcome to the forum.

did you try deleting .exd files?
https://social.technet.microsoft.com/Forums/ie/en-US/3f29c84b-97ad-46f7-9bc0-8648d69336ce/kb2553154-breaks-our-excel-macros?forum=officeitproprevious

[Weaholt]

welcome to the forum.

did you try deleting .exd files?
https://social.technet.microsoft.com/Forums/ie/en-US/3f29c84b-97ad-46f7-9bc0-8648d69336ce/kb2553154-breaks-our-excel-macros?forum=officeitproprevious

there don’t appear to be any. Although our systems are heavily restricted with what we can access.

[snb]

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.

[Weaholt]

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

[snb]

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

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

[Weaholt]

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.

[Weaholt]

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

[Weaholt]

thank you

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

[Weaholt]

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?

[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


27518


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

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

[Jan Karel Pieterse]

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

[Paul_Hossler]

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


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'

[Weaholt]

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


27518


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

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

[Paul_Hossler]

JKP did all the work

[Weaholt]

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

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

[Weaholt]

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

[Jan Karel Pieterse]

What reference is blown every time? MSForms?
I'd like to have a look at your file again if possible. One that does and one that does not exhibit the error if possible. This is an issue that is very rare and shouldn't be happening.

[Weaholt]

Thank you.

To be able to upload a example were the error is occurring, may not be that simple. Because Ihave to remove all of the data, names etc from the workbook, I can’t justupload a copy with the error because once the error occurs I am unable toaccess the worksheets to remove the sensitive info. I will have to keep openingand closing an amended version to see if I can get the error to occur.
As soon as I can get it to happen, I willupload.

Thanks again for your help.

[Jan Karel Pieterse]

I know this is normally against forum policy, but you are welcome to email the file to me (address is at bottom of my website) and I'll even sign an NDA if required (and acceptable to me of course).