How to allow Macros on a protected worksheet in Excel | Excel On The Go

[@Matt-pw7tg]

Great video. Does this work if I have more than one sheet in the workbook that is protected. I had it running properly for one sheet but when I protected the second sheet it stopped working. Any help appreciated 😀

[@mr.write1433]

omg i tried it now and its working men you save me lol.. Some of my codes didnt work on unprotect prot method but this one does . lol thanks a lot bro! this method is so rare and simple. i fortunately click your video !!

[@ExcelOffTheGrid]

All I can do is share what I know or discover, so I’m glad I could help 😁

[@royburtocobb]

Worked perfect! You the man! Thanks

[@ExcelOffTheGrid]

You're welcome!

[@scottrochford5523]

Hi mate, I am getting runtim 9, script out of range. I cannot work out why. Sheet name etc is correct. I have sent you a screenshot to your PM's. Any help would be great! EDIT: I cannot send you a PM on youtube :(

[@houstonsam6163]

Many thanks, this will be extremely helpful for the development projects I take on, including one I'm finishing up right now.

[@ExcelOffTheGrid]

Great news, I'm glad I could help.

[@wolfgangweiler177]

Thanks! Iike to learn from your site!

[@LordBransty]

You're a godsend. I've subscribed.

[@ExcelOffTheGrid]

Wow, thanks!

[@ahasanularefin8214]

Many thanks for this valuable content. Great job!!!

[@ExcelOffTheGrid]

My pleasure! Glad it was helpful.

[@ianinholland8390]

Thanks, I've been unprotecting & reprotecting for years - this is so much better! Really grateful for the tip.

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@CovertOps1]

I really need some Excel advice as I'm still learning. I need to protect a sheet with an existing macro. The macro auto adjusts the row height subsequent to a search by keyword search bar. I used a FILTER ISNUMBER SEARCH formula. I'm planning to share the workbook later, hence the need to protect it. The cells with the formula are also the ones that the macro adjusts. Is this a dead end?

[@Tripool1]

This helped me so much... Tx man

[@danielosorio217]

I'm wondering if something has changed in Excel related to this topic about running macros in a protected sheet. I have a worksheet with three sheets: raw data, a sheet with buttons to run individual macros in a specific sequence, and a sheet with shapes showing calculations done through some of the macros on the raw data, and within this sheet a button to one-click run all macros in the specific sequence. I protected every sheet and yet without the step suggested in this video, all macros run. Am I missing something? Please keep in mind I am new to macros trying to make my way through them.

[@IvanCortinas_ES]

Excellent channel Mark. Excellent contents. Interesting, powerful, useful... Thank you!!!

[@ExcelOffTheGrid]

Thanks - I try to create useful content. 😀

[@lubna3002]

Thank you a lot but why I don’t get debug choice?

[@bethjovi]

How did you add the sub called allow macro? It showed up on your video but how did you get it in there?

[@ExcelOffTheGrid]

In the workbook module, there is an event called Workbook_Open.

[@minamishail_Tech2024]

Can we do a similar process when we work with externally protected workbooks, using power query or VBA?

[@ExcelOffTheGrid]

I'm not sure what you mean by "externally protected". It is possible to apply the UserInterfaceOnly property to any open workbook (provided you know the password).

[@minamishail_Tech2024]

@@ExcelOffTheGrid , I mean if I have 2 or more encrypted excel files as a data source, which is used to import\load data to another file using Power Query. Can I load data un the P.Q. query, by providing the encryption password?

[@ExcelOffTheGrid]

Encrypted Excel workbooks are a separate issue to this video. I don't think PQ can connect to an encrypted workbook is possible. Unless your macro: 1) Opens the workbook 2) Removes the password 3) Saves and closes the workbook 4) Refreshes the data 5) Re-opens the workbook 6) Re-instates the password 7) Saves and closes the workbook

[@mr.write1433]

This is my main problem i have a very complex macro.. if i protect and unprotect it will ask everytime i do something with password . Ill try this tomorrow

[@sheikhayaz9106]

Hi Macro working is good But xls file closed and after macro message debug So please. Send me answer Thank

[@KaylaShannon-x5m]

This trick isn't working for me: I have a Query Refresh attached to a macro button, but once the sheet is protected it's not able to refresh? UserInterfaceOnly:=True is also highlighted in red, but I'm unable to add the apparent comma and underscore in the code that's shown?

[@ExcelOffTheGrid]

Are you loading to a Table? They don’t expand on protected worksheets.

[@ApplyThoughts]

For shared files it is not working... can u please suggest what can We do for that

[@ApplyThoughts]

I found a solution for even shared mode files too

[@JaspreetSinghKaim]

Thanks Man, that was wonderful

[@ExcelOffTheGrid]

I’m glad you liked it.

[@kayleighf.6599]

I got it to work! Sorry if you read my last comment about how I was disappointed. Thank you. I will be looking into more of your work. Thanks so much.

[@ExcelOffTheGrid]

No worries! I'm glad you got it working.

[@edward9840]

Very useful. Thank you.

[@ExcelOffTheGrid]

Thanks Edward - glad it was helpful!

[@iamteeten]

Hope you can help me with my macro issues 😢

[@mr.write1433]

Bro i wanted my workbook to be protected everytime. I just allow some ranges to open. The problem is i connected my macro on multiple sheets. The problem is how can i deactivate 2 ranges from a different sheet at the same time. Allow ranges has different password on my workbook.

[@Bishnu-hh7rm]

i want to run automatic macro in some protected sheet. how it is possible?

[@ExcelOffTheGrid]

Just put the code to allow macros at the start of the code and it will work.

[@killerinJP]

@@ExcelOffTheGrid I got confusing again. so that code is used to "ALLOW" running macros without unprotect the sheet right? but why after call the function when open, it NOT allow to run the macro again?

[@nickshutte8369]

Brilliant thanks!

[@ExcelOffTheGrid]

You're welcome!

[@iknow3335]

It works. However my use case is when 50 ppl using the same excel, then this unprotect protect looks like to cause an issue of "workbook looked by someone else", which end up me not being able to deploy new excel version (overwrite the same excel file). No way out at this moment. I can only unprotect all sheets at this moment.

[@s1ngularityxd64]

exactly what I need. 🥰

[@ExcelOffTheGrid]

Good stuff 👍

[@louisbuys6886]

Hi Mark. I used a macro to highlight the active cell. Got the 1004 error and when trying to apply your solution, I got a Runtime 9 error. Can you spot the problem😁. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A100") = ActiveCell.Address End Sub Private Sub AllowMacroWhenProtected() Sheets("sheet2").Protect Password:="Password", _ UserInterfaceOnly:=True End Sub

[@ExcelOffTheGrid]

Runtime 9, Subscript out of range - means you have asked for something that doesn’t exist. For example you might have a sheet called “Sheet 2” rather than “sheet2”.

[@theforgedone]

Doesn't this mean the user could open macros and see the password? What if we don't want to reveal the password?

[@ExcelOffTheGrid]

You would need to password protect the VBA project. Then users can’t see the code. But most passwords are useless anyway as shown by this post: kzbin.info/www/bejne/qXbCnaGIoL2IrdE

[@theforgedone]

@@ExcelOffTheGrid Wow, thank you for that information!

[@ExcelOffTheGrid]

No problem 😁

[@Robbie1987a]

And the best thing with this property is that it allows sorting locked cells throu VBA! So you don't need to unprotect your worksheet every time you want to sort in VBA procedure.

[@ExcelOffTheGrid]

I know, pretty useful right 😁

[@claude-marcelgrand-pierre9795]

Works for one sheet this is great now how do I protect the second one? lol

[@michaelangellotti5741]

I'm tired of unprotecting & protecting again. Many thanks.

[@ExcelOffTheGrid]

I know that feeling 😂