VBA luddite

[Marvyn]

Hi.

Apologies for my lack of understanding of VBA (and any forum rules inadvertently broken here) but I'm looking for help.

I have created a workbook with multiple sheets which various employees can input on. There is a Summary sheet at the front which changes when someone inputs on the other sheets.

I have managed to work out the code for this but need to protect the Summary sheet from being altered by users.

I've found various suggestions around the UserInterfaceOnly function but everything I try stops the code working from the other sheets (ie. the Summary sheet no longer updates when something is input on another sheet).

Ideally, I'd like the Summary sheet to be 'read only' but still allow macros from other sheets to alter it and for this to remain the case even if the workbook is opened/ closed.

Many thanks in advance for any help.

Should have said - if you are able to be quite specific with any suggestions I'd be really grateful; my knowledge of VBA is non-existent!

 

[Joe4]

Welcome to the Board!

You will want to lock/protect all the cells on your Summary page so that they cannot be altered.
See here for how to do that: Lock cells to protect them

How exactly is your "Summary" sheet being updated?
If by formulas, then you shouldn't need to do anything at all. Protecting the sheet will not prevent the formulas from recalculating.

If the Summary sheet is being updated by VBA code, then you will want to update that VBA code to temporarily unprotect the Summary sheet before making changes to it (and then have VBA re-protect it after making the updates).
This link shows you how to protect/unprotect sheets in your VBA code: VBA Protect / Unprotect Worksheets - Automate Excel

 

[Marvyn]

Hi Joe.

Thanks for the reply.

The Summary sheet is being updated by VBA code.

Where would I include the

Worksheets("Sheet1").Unprotect "Password"

?

Would it come before my Sub routine or should it be contained within it? (Again, apologies for my ignorance...)

 

[Joe4]

You want to put it right in the procedure, i.e. structured something like this:

Sub MyProcedure()

    Worksheets("Sheet1").Unprotect "Password"

'   Your code to make changes to Sheet1

    Worksheets("Sheet1").Protect "Password"

End Sub

The flow is quite logical:
- First, unprotect the sheet
- Second, make the desired changes to the sheet
- Third, re-protect the sheet

And you will want that in any procedure that is supposed to make changes to the sheet.

One thing to note/watch for is if you have any "Exit Sub" statements in your code (or anything else that might kick you out of the code before hitting the end).
Because that could unlock the Sheet and not re-protect it. You want to make sure that all exits re-protect the sheet.

 

[Marvyn]

Brilliant - thanks for the help Joe!

 

[Joe4]

You are welcome.
Glad I was able to help!