1

I have a challenging issue with an XLSM file with some macros in a multi-user environment. In this file I have some macros, amongst others one when the file closes - it hides certain sheets, and one that shows those sheets again - that one runs on start-up.

So this uses:

Private Sub Workbook_Open()
'Code to unhide some sheets
End Sub

and

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Code to hide sheets
End Sub

Now the next situation happened: 2 users (A and B) were in the same file together (on a Sharepoint location), so were working on it together. Next, user B leaves the file and the file runs the "close"-macro. So user A sees the sheets hiding and also closes the file. Next, user A opens the file again and all macros stopped working. No warning, no changes in settings, but none of the macros in the file will run - although my custom menu bar will show.

That exact same file works perfectly fine for user B and for me (let's call me user C), but on the PC of user A, the macros simply won't fire anymore (they did that morning). Does anyone have a clue what happened and/or what I can do about this?

Based on the comments, we did these tests:

  • move file on PC of user A to another folder (desktop) - no change
  • send file to user C (me), add a sheet, send it back, "unlock it" (through Windows Explorer - unblock macros) and open it - no change
  • sister file (100% the same macros, locked and signed) - works fine, macros work
  • file opened by user B (on the same network/company) and user C (external party, me) - works fine, macros work
  • file opened that morning - worked fine, macros work
  • double checked VBA settings - looks fine, no blocks there
Improve this question
6
  • 3
    The user may need to unblock the file under the file properties, add the sharepoint site to their trusted locations under their excel software. Do they have any ribbon appear when they open the file mentioning this? Commented Aug 27 at 20:13
  • 1
    Yeah, I would suspect something with Sharepoint going not well, like locked file, file locked in some particular state, etc. I would try refreshing somehow shared drive on user's PC Commented Aug 28 at 6:57
  • @MarkS. The curious thing is that Excel doesn't show any error message or red/yellow bar for that user. We did double check the file properties, no issue there neither. The file simply opens but macros don't work and give no error message - as if the setting for that file is "no macros allowed" (the Trust centre shows that macros are allowed). The weirdest thing is that that same file for another user works fine. And a "sister file" (same type, with macros) opens fine - with macros active - for the user that has the issue... Commented Aug 28 at 6:58
  • 2
    Try sync the XLSM to the users PC then open that. Also tag your question with Sharepoint so that users with Sharepoint can help. Commented Aug 28 at 8:30
  • 2
    We've experienced similar issues with Excel files in Sharepoint (Teams and OneDrive) - we frequently get a cached version of the file returned and this has led to strange behaviours with macros enabled books. Our "expert" kept telling us to clear our Office Cache (which is blocked for us). Not much help I'm afraid, but you are not alone, and it is almost definitely a Sharepoint issue Commented Aug 28 at 10:29
Related questions
651
How can I avoid using Select in Excel VBA?
0
Excel xlsm file macro not showing
606
Is there a way to crack the password on an Excel VBA Project?
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.