How to allow Macros on a protected worksheet in Excel | Excel On The Go
ฝัง
- เผยแพร่เมื่อ 23 ส.ค. 2024
- ★ 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
![อยู่คนเดียวกับเขา - ส้ม มารี (Zom Marie) [Official MV]](http://i.ytimg.com/vi/gq1cWAIQDLg/mqdefault.jpg)
Thanks, I've been unprotecting & reprotecting for years - this is so much better! Really grateful for the tip.
Glad it was helpful! 👍
Many thanks, this will be extremely helpful for the development projects I take on, including one I'm finishing up right now.
Great news, I'm glad I could help.
Thanks! Iike to learn from your site!
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 !!
All I can do is share what I know or discover, so I’m glad I could help 😁
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.
No worries! I'm glad you got it working.
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 😀
Excellent channel Mark. Excellent contents. Interesting, powerful, useful... Thank you!!!
Thanks - I try to create useful content. 😀
Worked perfect! You the man! Thanks
You're welcome!
Many thanks for this valuable content. Great job!!!
My pleasure! Glad it was helpful.
You're a godsend. I've subscribed.
Wow, thanks!
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.
I know, pretty useful right 😁
This helped me so much... Tx man
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?
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.
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
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 :(
Thanks Man, that was wonderful
I’m glad you liked it.
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.
Very useful. Thank you.
Thanks Edward - glad it was helpful!
Thank you a lot but why I don’t get debug choice?
exactly what I need. 🥰
Good stuff 👍
Brilliant thanks!
You're welcome!
How did you add the sub called allow macro? It showed up on your video but how did you get it in there?
In the workbook module, there is an event called Workbook_Open.
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?
Are you loading to a Table? They don’t expand on protected worksheets.
Can we do a similar process when we work with externally protected workbooks, using power query or VBA?
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).
@@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?
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
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.
Hi
Macro working is good
But xls file closed and after macro message debug
So please. Send me answer
Thank
Hope you can help me with my macro issues 😢
For shared files it is not working... can u please suggest what can We do for that
I found a solution for even shared mode files too
Works for one sheet this is great now how do I protect the second one? lol
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
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”.
I'm tired of unprotecting & protecting again. Many thanks.
I know that feeling 😂
i want to run automatic macro in some protected sheet. how it is possible?
Just put the code to allow macros at the start of the code and it will work.
@@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?