User level security in Excel Workbook for different worksheets
HTML-код
- Опубликовано: 4 апр 2020
- User level security in Excel Workbook for different worksheets
#VBA #WorkbookSecurity
In this video, you will learn how to create the User level security in Excel workbook for different worksheets.
Please download the practice file from below given link:
www.pk-anexcelexpert.com/user...
Download our free Excel utility Tool and improve your productivity:
www.pk-anexcelexpert.com/prod...
See our Excel Products:
www.pk-anexcelexpert.com/prod...
Visit to learn more:
Chart and Visualizations: www.pk-anexcelexpert.com/cate...
VBA Course: www.pk-anexcelexpert.com/vba/
Download useful Templates: www.pk-anexcelexpert.com/cate...
Dashboards: www.pk-anexcelexpert.com/exce...
Watch the best info-graphics and dynamic charts from below link:
• Dynamic Graphs
Learn and free download best excel Dashboard template:
• Excel Dashboards
Learn Step by Step VBA:
• VBA Tutorial
Website:
WWW.PK-AnExcelExpert.com
Facebook:
/ pkanexcelexpert
Telegram:
t.me/joinchat/AAAAAE2OnviiEk5...
Pinterest:
/ pkanexcelexpert
************ Suggested Books ********
VBA: amzn.to/2TMMikX
Excel Dashboard: amzn.to/2WZi2Fj
Power Query: amzn.to/2Ibd7xR
Power Pivot and Power BI: amzn.to/2DCg8BB
Exam Ref 70-778 (Power BI): amzn.to/2GnWYTN
************ My Stuff ***************
Mic : amzn.to/2TLnF88
Video Editor: screencast-o-matic.com/refer/...
This is amazing - never did any VB programming in Excel but this is a great way to introduce it!
This tutorial was awesome.I can say it was the most useful RUclips video for me! I was searching for a solution for exactly the same requirements and you saved me. Thank you so much
Hi, first I would like to say that you have a gift for teaching. I've watched many videos on VBA, although they were great as well they lacked the ability to teach they mostly showed the formula, lastly, your code is easy, to the point with needless excess. I've learned a lot from this video and of course I'll subscribe and learn more from your channel. Thank you💯👍
This is a fantastic solution and easy to follow, I have viewed other videos and they are not even close to this guy. Excellent and thanks very much for providing this, really appreciated!
Glad it helped
I'm a VBA novice. Started working with VBA some two weeks ago. Your approach and presentation is fantastic, even for beginners. Thank you from South Africa.
Only issue is that Trust Center can disable macros and subsequently any user login forms. Effectively rendering login security useless
I´m also a beginner in this VBA but theese videos are amazing. Some of the things you show I can use with small changes to fit my needs. Thumbs up from Sweden. 🙂
Glad you like them!
So inspiring to see how excellent you are. Very well done.
Thanks for your valuable feedback
I am Egyptian and I was very happy when I followed you, and I hope you explain how to write symbol inside the code editor in Excel
Fantastic, bro....
U explained it very well than others....
It is exactly what I need in my work...
Thanks....
Thanks for your valuable feedback
Thank you for the step-by-step instructions for this project. Your approach and presentation is beginner - friendly. I have subscribed to your channel and I cannot wait to learn even more.
Thanks a lot🙏
Excellent. Very useful. Got it at the right time.
Glad it helped!
Man.. You are amazing. Seriously hats off 👍 Just subscribed your channel. Keep doing the grate work and keep inspiring us. Thanks for this amazing video.
Thanks for your valuable feedback
Thanks for sharing this wonderful video 👍🙂
Thanks for your valuable feedback
Excellent video and explanation!! If we put a filter to see only a particular type of security, how do we do so that when the filter is displayed we see the figures and not the characters? Thank you.
I am speechless my friend, excellent work
Thank you very much!
Excellent video. I have been trying to figure this out for a bit and this video was perfect. Thank you for the video. Subscribing to learn more.
Thanks for your valuable feedback🙏
Hi, thank you so much for your video. Just wondering is there a way to stop the last saved page from flashing up before the login piece appears?
Amazing Bro, really you are and expert.
Many many thanks
Thank you very much!!!
You're welcome!
Great content and demonstration,
How could we lock a specific user after 3rd attempt?
I must say this type of teaching and explanation was so good and the workbook is awesome mind blowing fully advanced........
Now i have only one doubt or requirement now how i will see witch entry done by which user please help me or send program to add that.
subscribed & definately thumbs up. Thanks for teaching PK. Excellent tutorial
Thanks a lot🙏
how can i view the user management form? when I login as admin level, it only shows me 2 sheets. thanks a lot@@PKAnExcelExpert
you are amazing keep up the great work
Thanks for your valuable feedback
Thanks a lot buddy ! It helped :)
Thanks for your valuable feedback
Really it's Awesome and way of your teaching is extremely too Good 😊👍
Thanks for your valuable feedback
@@PKAnExcelExpert pls share your email id for further clarification, if any will get in touch with you
thanks !!! very good !!!!
Thanks for your valuable feedback
Hi, thanks for the video it's really good one, my only problem is to make one worksheet protection we have do all this, can you make video in which, user have to click on button it will open browse option to select sheet. Then macro will show us the user management page, user have to fill the data and it will convert and save the file in protected for ( in short converting any excel worksheet into protected form)
Thanks a lot
Super, Sir....
U explained it very well than others....
It is exactly what I need in my work...
Thanks....
But i ask you one think if i have more than 4 column then what i will do how to increase columns..
Please reply
I appreciate the sharing of your knowledge.
I'm applying to a job.
I would like to get your answer to a question
add one more option to the symbols. (No access, write access, read access and 4th access to hide a column)
It is possible to help thank and wait
Hello PK,
Thanks for sharing this video , it's very helpful.
I am having similar type requirement in my project and implemented same logic which u shown in video but when I increase the column with more sheet , lock that is yellow below symbol logic is not working for rest sheets , it's working only for 1st 3 columns sheets.
Wsh.Visible = xlSheetVisible
Wsh.Protect 1234
Rest all logic working ,only facing issue for above.
Please reply
wonderful tutorial...thanks....can you also please make a video explaining how to protect our VBA Macro-codes from opening up in LibreOffice / Ubuntu...i have put a password and blocked for viewing, but still they easily open up in LibreOffice...
Bhai undoubtedly you did a fabulous job and you are already an expert in Excel... Kudos for that... But isn't this the work of Randy Austin?
Thank you for this video.
I am interested on making an registration from that users can use it to auto populated this log in information. Granting that new user a minimal access into the admin change the access level.
Can you help me with that?
Thank you again
Brilliant, I was looking for something like this on Friday. Is there a way to show the same sheet but only certain information to the users?
Thanks for your valuable feedback. Yes we can do that. I will definitely try to make such
@@PKAnExcelExpert Thank you so much for sharing your knowledge with us :)
Hi PK! Will the users have access like as shown in your video if I shared the workbook on Google sheets?
This is really useful, however I found that whenever the code runs it forces to lock the sheet with a default set of conditions.
However I would like it to lock the sheets with different locking conditions. For example, I want to lock sheetA but the user can still format the rows or edit objects.
Your video however, force lock the cells where they can only select and unselect locked cells but other rules are ignored even though I already preconditioned the locking rules for each cell.
Can you please make a video that has this security feature but will lock the sheets with it's own protection conditions? ( like Format rows/cells/columns, edit objects and the others?)
And also with a code that will force a scenario where the user has to enable the macro feature when they first open the file? 🤔
For all having "Runtime error 9 subscript out of range", I have solved by adding "On Error Resume Next" just before "Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)", like this:
On Error Resume Next
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)
Hope this helps!
Unfortunately doesn't get rid of the problem for me 😢
very nice , it very useful for me
Thanks for your valuable feedback
Sir i have also seen yr inventory management and attendance management tuitorial, they are very nice, thanks u
Thanks for your valuable feedback
@@PKAnExcelExpert please make dynamic balance sheet, ledger, profit loss Statement
super video, dziękuję :)
Thanks for your valuable feedback
@@PKAnExcelExpert sir, can I have your E-mail Id please.
Bahut hi lajawab sir
Thanks for your valuable feedback
Wow amazing
Thanks🙏
Thank you sir
Thanks for watching
Very interesting Sir. Brilliant but my question is if one user working on that sheet so how other can work on that because excel only allows other users read only. Please give me solutions how multiple users can work on same file without read only.
Hello Mr. PK,
Interesting file, thank you for your support.
I would like to ask you if this will work also in share mode? Will be able more than one user to work online on it?
Thank you very much!
Hi Mr.Bogdan & Mr. PK, I wanted to ask same question. Request you to share your thoughts.
This was very helpful and excellent work.
VBA, for now, doesn't work in the browser. It works only if you first run it in a desktop app.
Superb!
Thanks for your valuable feedback
Thank you.♥️
I requesting you to make video on file transfer(copy.paste) from 1 folder to other multiple folder's..
Sure, I will definitely try to make such video
@@PKAnExcelExpert sir awaiting for video
Brilliant,👍👍❤️
If a user login and some data entry, the name of the login person next to the data entry will automatically appear?
Please how to create,,,
If the excel contain pivot table and the data are in table (control + T) , is it still able to coding the user management?
Grt.....PK
Amazing work !
I need to share the file with other users on different devices how can i share it and make it work on other devices ?
Thanks in advance
Hello PK, I tried this twice, went through the entire process, but kept on getting some or the other error. Is it possible for you to upload the file you have created here so that we could make some good use of it. Thanks.
Thank you for the valuable sharing. I have much benefit.
I got "Method or data member not found (Error 461)" error due to
'With frm_Password_Reset
.txt_UserName.Value = Me.txt_UserName.Value'
due to ".txt_Username.Value"
What can be the reason ?
PK Thanks a lot. Love your presentations. It will be very helpful for me. However, how could we force an users to open the workbook in macro enable mode? Also, I would like to know what is the best way to share this workbook with users and work on a collaborative manner.
Hi @swastikhotmail. Were you able to ever find a solution to the latter question? Coming across the same issue now. Thanks in advance!
@@MrBrianpark123 Not yet
@@MrBrianpark123 Hi, did you find the solution for this? Thank you
@@jeffnoahmendoza2777 Unfortunately no
Brilliant.. u r talented
Thanks for your valuable feedback
@@PKAnExcelExpert you copy randy project from excel for freelancer
@@PKAnExcelExpert please I keep getting an error saying “method hidden of object range failed” help me please
Sir need some help...this is excellent work...just need to add one more thing in it...that..once the user login successfully ..then everything is hide and userfom 2 should be visible..that's what I want..
Thanks and regards
nice take care have a great day
Thanks
Hi.. Thank you for a wonderful explanation for the same. I have a question. If I share a Macro Enabled workbook like this through email, the user when he downloads this in his machine the macros get disabled and he is able to see everything. How to implement this user level security hack and distribute this file to the different users. I do not want the users to see the sheets that are hidden to them through macros. Please help.
Yayyy thank you so much, PK! However it doesn't apply to office 365, is there any way to create the same settings on sharepoint / office 365? Hoping for another tutorial :)
i am also lookng for share point
Can multiple people login in this excel file with different login ,when kept on shared drive and save work?
Thank you PK for the excellent user login excel work.
Please how do I change the code so instead of opening the User Management sheet:
1. it will rather open a Main Menu sheet for all Users
2. open a Main Menu sheet and the User Management sheet for only the Admin
Helo PK, this very amazing.. but i have question....
how to make excel sheet file will only load up/show up after login succed. so if after click excel file, it will appear/load up the login form only., then after succed login, the sheet will load...
Because if the user not clicking on "Enable Content" VBA, the login form not load up, and the user can access all the sheet and they can make edit. thanks
Thank you. Your download link is little bit difficult the practice file. May be change it for direct link.
Very useful
Thanks for your valuable feedback
Can we make a condition that same workbook can be access by all users and allow to save changes at the same time
Hi PK - thank you for the excellent tutorial video. Do you know how we make this a shared workbook for multiple users to make edits at the same time? It seems like it loses its capabilities. Thank you
Hi @chihuahua7740. Did you ever find a solution to this? Coming across the same issue now. Thanks in advance!
@@MrBrianpark123 I am finding same problem. If we put this in shared drive it looses its capabilities
Thank you for this tutorial. Very thorough and detail. I wrote the code and followed your instructions but I am coming across a run-time error 9 subscript out of range under Else ‘for user. The debugging highlights set wsh = ThisWorkbook.Sheets (sh.Cells(2, I).Value) would you know how to fix this issue?
Please check the worksheet name is correct or not.
I have the same issue at the same point in the code! Followed along step by step but got the run-time error 9
The code worked fine, infact it runs superb but there is a problem, while sharing this file over email. The recipient will be able to see all sheets as macros wont be enabled on his PC. Any code to force run macro before user open this sheet or keep the sheets hidden until or unless macro is enabled. Your quick response is appreciated.
I want to use track changes feature and lock certain cells so that data in such cells is not changed. But because of VBA i am not able to use it directly. Can you please tell me what addition vba could be done so that i can use both of the above features.
It's working for mr very nice
Thanks for your valuable feedback
@@PKAnExcelExpert can you please help me, user getting error message when they working in the sheets at a time, I shared this file in SharePoint
Do i have to save after writing every code? To see whether its working or not?
than you sir
Welcome
thanks
Health to your language (Turkey)
Sir can we add the activation button to run in permitted system only., so that it can not be copied to another system without admin permission and also after perticular time excel should bot open and admin can give permission to run again for specified period.
Very nice sir
Ek achchhi job k liye excel me kya. Kya sikhna chahiye
PK, i am trying to use this through the online version of excel that i use in one drive for my company. Can you tell me how to make this work?
Hi sir
Your information is very much valuable for me.You r a legend.
I have 2 doubt
1-Sir the same security can be apply for Google drive.if it is please possible please let me know.
2-I tried to share the same excel ,but the VBA was not working.if it is possible to share the same with these vba
Fantastic
Thanks for your valuable feedback🙏
Can we put the file in sharing folder and enable multiple users?
Thanks for this video to learn.
May i ask some questtion.
1. I make a file the same file you share in this video (book 1)
2. Admin can show all sheets.
3. Someone there is piority user show sheet1 (sheet2 , 3 very hidden)
4. I open another file (book 2). Login by user piority. I type vba code in book2:
Sub show_all_sheet
Dim wb as workbook
Dim ws as worksheet
Set wb = workbooks("book1")
For each ws in wb.worksheets
Ws.visible = xlsheetsvisible
Next ws
End sub
5. Now user use not admin but user can show any sheet in book1 ( the same hack)
How can i veryhidden sheet. But some one can not visible sheet (can not hack)
Thanks you so much!
I cannot see your screen clearly on my mobile.
You are genius in excel.
I see after the macro using quality 480p
Good Video. I am having a problem when inserting my own users and sheets. On the Admin Role everything works fine on the User Role I get an error which bring me back to the VBA screen with this line highlighted. Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)
Can you think of any reason why?
did you solve this problem? i also got this error
@PK: An Excel Expert - Bro, everything working fine...but user management sheet is getting hidden even for admin....please help
😍😍😍
Thanks
Excellent.
I have tried this and its working like a standalone app. However, when I uploaded the excel online and opened through Office 365. Its not working. Its opening without asking for a password. Please help.
Thankyou so much... please let us know how to lock the vba...
I will create a video on this topic
How can | make it to where the user
is required to use the correct case?
Right now, the user can login as long
as the characters match.t.even if it
doesn't match each upper or lower
case for the password‘
I have tried to use the code but i get error that says Compile error (Block If without End If) then when I click ok on the error box it points back to this and yellow fill (Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean). What can I do to have it running in my workbook because I have followed exactly what you are doing on screen.
Can’t we use this type of setup in OneDrive online mode
Thank you for the great tutorial! I was wondering if it's possible to create employee credential validity. Let's say Raj has his username and password working only for 30 days, then asks for Admin Password. Is this possible ?
Yes you can! We need to modify the code accordingly.
I have tried getting started on VBA serval times, but it never works out for me. You explanation is simple & I followed it step by step; but when I go to run it; i keep getting this error "Compile Error: Variable not defined" & this "Private Sub UserForm_Activate()" is highlighted yellow. I even tried creating a brand new form, & its still the same :(
v.good
Thanks for your valuable feedback
nice video
Thanks
How can I share this to others so they can edit it and do some changes like I sent it to someone and he has the same access as I do ?
In this file how I can write a code to always open a sheet named homepage
Sir,
I request you to make this video on Google Sheets too.
Thank you
I’ve been searching for this, is it possible to allow an user and password for only a computer, ip? Like the person could not share his user to another person
Yes, it is possible. I will try to add in next version.
Dear Sir, if the user is allowed to view only on protected sheet, they can unprotect by right clicking sheet tab. Please share solution for this
Hi Sir, how can i valid the reset password form twice like { user should enter the new password twice and if both the times entered password is correct then only the new password should get updated else again the user has to enter twice}....
Can you please help me by checking my VBA, it is showing an error ask me to debug and marked as yellow. any suggestion will be helpful. Though, i'm following your steps very carefully it is still the same.