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

★ Want to automate Excel? Check out our training academy ★
exceloffthegri...
★ About this video ★
In this video we show you how to easily enable macros on an protected excel worksheets.
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegri...
★ Where to find Excel Off The Grid ★
Blog: exceloffthegri...
Twitter: / exceloffthegrid
#MsExcel #PowerQuery

[@ianinholland8390]

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

[@ExcelOffTheGrid]

Glad it was helpful! 👍

[@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 😁

[@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!

[@IvanCortinas_ES]

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

[@ExcelOffTheGrid]

Thanks - I try to create useful content. 😀

[@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 😀

[@royburtocobb]

Worked perfect! You the man! Thanks

[@ExcelOffTheGrid]

You're welcome!

[@ahasanularefin8214]

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

[@ExcelOffTheGrid]

My pleasure! Glad it was helpful.

[@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.

[@LordBransty]

You're a godsend. I've subscribed.

[@ExcelOffTheGrid]

Wow, thanks!

[@Tripool1]

This helped me so much... Tx man

[@JaspreetSinghKaim]

Thanks Man, that was wonderful

[@ExcelOffTheGrid]

I’m glad you liked it.

[@edward9840]

Very useful. Thank you.

[@ExcelOffTheGrid]

Thanks Edward - glad it was helpful!

[@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 😁

[@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 :(

[@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

[@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 👍

[@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?

[@nickshutte8369]

Brilliant thanks!

[@ExcelOffTheGrid]

You're welcome!

[@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.

[@lubna3002]

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

[@claude-marcelgrand-pierre9795]

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

[@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.

[@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.

[@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.

[@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

[@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

[@iamteeten]

Hope you can help me with my macro issues 😢

[@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 😁

[@michaelangellotti5741]

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

[@ExcelOffTheGrid]

I know that feeling 😂

[@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?

[@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”.