Excel VBA Macro: Run Macro on Protected Sheets (with Password)
ฝัง
- เผยแพร่เมื่อ 11 ก.ย. 2024
- Excel VBA Macro: Run Macro on Protected Sheets (with Password). In this video we go over how to unprotect and protect a protected sheet, in order to allow our macro to run but prevent the user from altering the data. We also go over how to unprotect/unlock a single cell on a protected sheet.
Data used in this video:
gsociology.ica...
#ExcelVBA #ExcelMacro
![[FULL EP.73] เปิดบ้าน 70 ล้าน "แป้ง Zbing" ยกมาทั้งญี่ปุ่น l เคหสถานบานปลาย l One Playground](http://i.ytimg.com/vi/YBnJhTt07Pg/mqdefault.jpg)
Thanks!
Wow! Thank you so much!
For somebody who is new to VBA, this was a simple code and your instructions are very easy to follow. Thank you!
So glad to hear that! Really appreciate the feedback!
That was easy to understand and to apply, thank you!
I have been scouring the internet for how to do this, this video is the only correct way I've found so far. Thank you!!
So glad to hear that! Thanks for watching!!
Very clear, concise and beneficial - thanks
No problem! Thanks for watching!!
Thank you very much, bro!
This was exactly what I needed for finishing a project from work before going to the airport to finally be on vacation! :D
No prob! Happy to help!!
Hi Greg.
I found it better to declare a public constant at the top where you store the password and use that in the .Protect and .Unprotect parts.
Something like this: Public Const pwd = "greg123"'
Then you could use: Thisworkbook.Sheets("Sheet2").protect pwd
I did it exactly like you did for a long time, then I found that we can declare Public constants
That way if you have to change the password you only have to do it in one place.
Also since anyone can see the password once they open the macro it is adviseable to lock the VBA project from viewing. in VBA window-Tools-VBA Project Properties-Protection tab.
Thanks for sharing that!!
Thanks! I’m not a coding person and you solved my issue very simple.
No prob! Happy to help!!
I needed this man, thanks!
Oh my gosh, thank you thank you thank you!! I have spent a ridiculous amount of time trying to get this to work, using all sorts of other sites. Your solution worked first time!
you're very welcome!
Great tip, thank you!
Best trick ever!!!! Was looking for this for so long. Thank you!😀
No prob! Thanks for watching!!
Awesome!!! Really needed this, thank you very much!
No problem! Glad to hear that!!
Thanks so much for this. Such a simple answer but I was totally lost. (headbang)
No prob! Happens to the best of us! Thanks for watching
Extremely elegant and useful! Thanks
No prob! Thank you!!
How to allow Insert Buttons option to run in protected sheets... ???? Make on video on it
Thanks a lot, I was looking for this for days...Thanks...Thanks...Thanks...
No prob! Glad it helped
I'm wondering if someone can help me...I did everything in your video and it works perfectly except for when the macro protects the sheet again, it doesn't keep all the cells I've formatted as unlocked. Some cells remain unlocked while others give the protected sheet error. Am i doing something in the wrong order?
This was very helpful. Thank you!
truly helpful , was not able to do that , watched more than 50 videos but your video actually helped
So glad to hear that! 😊
@@greggowaffles Hi I need some help with data entry , can you help me with it?
What exactly do you need help with?
@@greggowaffles There is one form i have created through VBA and it stores data into another sheet which I password protected , but whenever I submit data through that form , the data move to down the row . I want latest entry to come on top row ( Kind of automatic sorting)
@@greggowaffles Basically I want a form , in which when i submit data , data comes on another worksheet which is password protected and latest entry should come up as per date entry , latest date data should come up and older date data goes down automatically (on the sheet where data is being stored)
the best super super easy, thank you very very much
Thank you for watching!
very helpful
Glad to hear that!
What a legend, well done man, this helped a ton
Glad to hear that! Thanks!!
I protected my diagrams from being selected or edited with password but after applying some macro on the sheet the diagrams on the sheet became selectable and editable even protected with password. It like the protection does not work anymore when I apply micro. Please help.
thanky you,,, But autofilter button is not working by using this vba code... I have also seen some comments as autofilter is not working in this vba code.. can you reply please
It is asking me for the password whenever I try to change the cell that has macro data. TIA
Thank you so much :)
Thanks
This is genius.
Thank you!
Thanks It worked
No prob! Glad to hear that!!
It was very helpful 🙂
Thank you!
Helpful tips
Life Saver 😅
Happy to help!
thank you so much for this video
No problem!
Hi thanks for this video very helpful. I followed your step and it works fine, I do have a slicer as well. When I run the vba it protects the sheet again.
work flow is
1. run the code
2. Click a slicer to filter data.
However, the slicers don't work and say I need to unprotect the sheet first. Any thoughts?
Basically I wont to be able to run a macro and slicer simultaneously.
Can I VBA code the properties to protect such as allow to use auto filter (like those on the tick boxes when we do protect manually thru the interface?)
thank you for this!
No prob!
Great work.
Thank you!
thank you so much!!
No problem!!
Thank you!!!
No problem! Thank you for watching!!
the earlier problem was solved not It shows "Pastespecial method of range class failed". I have inserted the code in my previous comment
then how do you prevent somebody access to the VBA code? because they can just go there to see the password now which defeats the purpose of it all, no?
Yeah, they absolutely can. This video goes over how to prevent somebody from accessing the code: th-cam.com/video/aXnGg0aNQYs/w-d-xo.html
@@greggowaffles thanks a lot!
No problem!
thank you so much for this video, it works for me. When I protect the sheet manually, I always tick "use autofilter". However, the VBA command does not tick "use autofiler". Could you help me out?
Did you found out how to do this? I’m currently trying to figure it out!
Hey thanks for the informative video.
I had a doubt
Since the sheet is getting protected back everytime I seem to be loosing the previous check boxes I made
Such as edit rows and columns under the permissions given
Hey no problem! You'll have to have code that accounts for those parameters. If you "record" a macro while you lock your cells, you'll be able to see what code you need to do that. Hope that helps!
@@greggowaffles this was what I was asking also. @kengo did you manage to do it?
Can u share how to run VBA on a hidden sheets.
this works for a sheet and not workbook. is there a way to password protect the workbook and run the VBcodes?
All you have to do is take out the "Sheets" part:
ThisWorkbook.unprotect "password"
ThisWorkbook.protect "password"
just want to unprotect, in order to run the macro, and then protect it when the macro finishes running. Hope that helps!
Got it! Thanks!
@@greggowaffles hi, I have posted my query on one of the forum, could you help me please as I am unable to understand why the code is not working
www.excelforum.com/excel-programming-vba-macros/1329278-error-in-running-codes-in-protected-workbook.html#post5405614
Good👍
Thanks!
This works for a protected sheet and not a password protect excel file!
All you have to do is take out the "Sheets" part:
ThisWorkbook.unprotect "password"
ThisWorkbook.protect "password"
Hope that helps!
@@greggowaffles Just tried it and it didn't work for me, maybe I did something else wrong. Thanks anyway.
It says compile error, "the member already exists in an object module from which this object module derives"
you used the same name twice for a variable, function, etc. you have to change the name of whatever is causing the error
@@greggowaffles it works now thank you for the help, I also have one question whenever I close my sheet and open it again, the code doesn't work! But if I remove the entire code and paste it again while the sheet is open then the code works and functions properly! (I have saved my sheet as .xlsm
@@rishimervana5036 hmmmm...thats weird. i would have to see the code to figure it out
@@greggowaffles Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
unprotect_roger
If Not Application.Intersect(Range("C13"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "off-grid only": Rows("25:31").EntireRow.Hidden = True
Case Is = "on/off-grid": Rows("25:31").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("C32"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("33:43").EntireRow.Hidden = True
Case Is = "Yes": Rows("33:43").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("C38"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("39:43").EntireRow.Hidden = True
Case Is = "Yes": Rows("39:43").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("C44"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("45:54").EntireRow.Hidden = True
Case Is = "Yes": Rows("45:54").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("C49"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("50:54").EntireRow.Hidden = True
Case Is = "Yes": Rows("50:54").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("C56"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("57:66").EntireRow.Hidden = True
Case Is = "Yes": Rows("57:66").EntireRow.Hidden = False
End Select
End If
If Not Application.Intersect(Range("C61"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "No": Rows("62:66").EntireRow.Hidden = True
Case Is = "Yes": Rows("62:66").EntireRow.Hidden = False
End Select
End If
protect_roger
End Sub
Sub unprotect_roger()
ThisWorkbook.Sheets("InputPage").Unprotect "roger_federer"
End Sub
Sub protect_roger()
ThisWorkbook.Sheets("InputPage").Protect "roger_federer"
End Sub
Try deleting ActiveSheet.Activate. if its already the active sheet, you don't have to activate it
need your help please respond
Waaitt the macro is more interesting..!
Lol I have a video on that macro here: th-cam.com/video/dEAozqfZFHE/w-d-xo.html
Thanks! FYI, your Upwork link doesn’t work
Yikes! I gotta fix that. Thanks for letting me know!
It says compile error 🥲
I used to do it this way for years and for a long while now I have been using the UserInterFaceOnly:=True argument in the workbook module. Making this method that you propose verbose and obsolete.
I’ll check it out. Thanks for sharing that
Way to complicated