Excel Protecting multiple sheets with auto locking on closure

[andyd25]

I have been asked to setup a multi-user spreadsheet to record hours worked. Each user can only access their specific sheet and use a password to make changes. I then need the worksheet to automatically lock on closure.

 

I have enabled Macros and added the following

Sub ProtectALLShts()

Dim Pwd

Pwd = "1230" '<<< change password !!!

For Each ws In Sheets

If ws.ProtectContents = False Then

ws.Protect Password:=Pwd

End If

Next

ActiveWorkbook.Save

End Sub

 

I have then added

 

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.Run "ProtectALLShts"

End Sub

 

It worked great for one test user. I then duplicated the first section with a seperate password and then got an error message when trying to close the spreadsheet. Run-time error 1004 Cannot run the macro ProtectAllShts. The macro may not be available in this workbook or all macros may be disabled.

 

I have checked Developer Macro Security for Macro Settings and enabled Trust access to the VBA project object module.

 

Any ideas or different way to setup a spreadsheet to record work hours appreciated.

 

[Ditto]

An interesting problem! I'm working on a couple of very similar challenges.

 

I'm just wondering if you need to store the macro in Personal.xlsb to make it available to all users?

[simpsonj]

Apologies, I can't help with the Excel Macro, but I wondered whether you could collect the data a different way, such as using a Microsoft Form? This would then feed the data into a single spreadsheet which could then be manipulated however you needed the information?

[Ditto]

Another idea, try ChatGPT (or dedicated coding AI tool) - if you can explain the challenge well, it can give surprisingly reasonable solutions!

[Ditto]

An interesting problem! I'm working on a couple of very similar challenges.

 

I'm just wondering if you need to store the macro in Personal.xlsb to make it available to all users?

 

On second thoughts, probably not, as Personal.xlsb is really about having macros work in all workbooks, not a macro working for all users.