Help making recorded macro work on protected sheet

[EmmaOD]

Hi everyone, I'm working on a project at the moment which required a Macro to do the following:

(The button to activate the macro is located on a sheet called "Output" but all of the work takes places on one called "Calculation Sheet")

• Copy cell at "Calculation Sheet!AB503"
• Paste into a cell lower down as a value (Z506)
• Highlight Z506 and go into data>Text to columns
• Separate the data based on where semi colons are located and put the separated data into different columns (starting at A507)
• Delete contents of Z506
• Copy entirety of Row 507
• Paste (transpose) into AA511
• Delete entirety of Row 507~

• Copy cell at "Calculation Sheet!AB504"
• Paste into a cell lower down as a value (Z506)
• Highlight Z506 and go into data>Text to columns
• Separate the data based on where semi colons are located and put the separated data into different columns (starting at A507)
• Delete contents of Z506
• Copy entirety of Row 507
• Paste (transpose) into AB511
• Delete entirety of Row 507


• Go back to Cell A1 on Output Sheet


I recorded a macro to do this (and have cleaned it up slightly by removing "scrolling" stuff. This is what's left:

Please Login or Register  to view this content.

This code works fine as long as the Calculation Sheet is unprotected but I would ideally like to protect that sheet before sending it round to my coworkers (while ensuring the macro still works). Is there any way to do this?

Thank you in advance

E

[Olly]

At the top of your code, after the comments, enter the line:

Please Login or Register  to view this content.

At the end (just before End Sub), enter the line:

Please Login or Register  to view this content.

[EmmaOD]

Hi Olly, thanks for the reply.

Apologies, I should have made clearer that I don't want them to need to enter the password to unprotect the sheet if possible.

When I added in the lines of code I was prompted for a password before the macro could be run.

[Olly]

Okay, so change those lines to:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Change "foo" to your password, obviously.


If you take this approach, you may want to password protect your VBA project, to prevent users finding the password in unprotected code.

[EmmaOD]

You can do that? :P

[Olly]

Of course!

In the VBE (VB Editor window), click Tools > VBAProject properties. Choose the Protection tab, tick 'Lock project for viewing' then enter the password of your choice in both boxes. You'll now need to enter that password to access your VBA project, but macros will still run without needing to enter a password.

[EmmaOD]

Brilliant,

Thanks for your help Olly

Have a nice day!

[Olly]

Brilliant,

Thanks for your help Olly

Have a nice day!

Glad it helped Thanks for the feedback, and the rep.

[Olly]

Just a quick followup - your code could be more efficient. Try something like this:

Please Login or Register  to view this content.

[RonRich]

Oliy
I also found your solution very helpful and will use it in my own projects as a basic way to protect certain contents of sheets while still letting them be updated by macros. Thanks!!