User level security in Excel Workbook for different worksheets

Подписаться 272 тыс.

Просмотров 107 тыс.

50% 1

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/...

Опубликовано:

4 апр 2020

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 269

@cerberus205412 2 года назад

This is amazing - never did any VB programming in Excel but this is a great way to introduce it!

@saharsheri9623 2 года назад

This tutorial was awesome.I can say it was the most useful RU-vid video for me! I was searching for a solution for exactly the same requirements and you saved me. Thank you so much

@peterwilsnagh8403 Год назад

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.

@matthewgolaboff Год назад

Only issue is that Trust Center can disable macros and subsequently any user login forms. Effectively rendering login security useless

@PedroMartinez-nl1gr 11 месяцев назад

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💯👍

@paulhaslam3831 9 месяцев назад

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!

@PKAnExcelExpert 9 месяцев назад

Glad it helped

@njvanheerden2157 4 года назад

So inspiring to see how excellent you are. Very well done.

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@michaelsvenson2456 Год назад

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. 🙂

@PKAnExcelExpert Год назад

Glad you like them!

@vinothprakash1465 4 года назад

Fantastic, bro.... U explained it very well than others.... It is exactly what I need in my work... Thanks....

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@afifimaher2177 2 года назад

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

@reggieo4889 8 месяцев назад

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.

@PKAnExcelExpert 8 месяцев назад

Thanks a lot🙏

@VipinThakur 3 года назад

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.

@PKAnExcelExpert 3 года назад

Thanks for your valuable feedback

@ArmanKhan-iq3ji 3 года назад

Thanks for sharing this wonderful video 👍🙂

@PKAnExcelExpert 3 года назад

Thanks for your valuable feedback

@arulprakash5606 4 месяца назад

Excellent. Very useful. Got it at the right time.

@PKAnExcelExpert 4 месяца назад

Glad it helped!

@nooramin5386 Год назад

I am speechless my friend, excellent work

@PKAnExcelExpert Год назад

Thank you very much!

@johncline7215 Год назад

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.

@PKAnExcelExpert Год назад

Thanks for your valuable feedback🙏

@MohamedElAbassy2 4 года назад

you are amazing keep up the great work

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@princeshekhar7375 4 года назад

Bahut hi lajawab sir

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@gerrysotto9688 Год назад

Thank you very much!!!

@PKAnExcelExpert Год назад

You're welcome!

@cristiansierra6284 Год назад

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.

@ijnmpi 4 года назад

Thanks a lot buddy ! It helped :)

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@jgun0075 4 года назад

Really it's Awesome and way of your teaching is extremely too Good 😊👍

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@jgun0075 4 года назад

@@PKAnExcelExpert pls share your email id for further clarification, if any will get in touch with you

@KDJha 2 года назад

Amazing Bro, really you are and expert.

@PKAnExcelExpert 2 года назад

Many many thanks

@romlef526 4 года назад

thanks !!! very good !!!!

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@adminschroll9129 2 года назад

Great content and demonstration, How could we lock a specific user after 3rd attempt?

@jonfkerr 10 месяцев назад

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?

@manishmamidwar 2 года назад

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.

@Jojosmith342 Год назад

subscribed & definately thumbs up. Thanks for teaching PK. Excellent tutorial

@PKAnExcelExpert Год назад

Thanks a lot🙏

@Jojosmith342 9 месяцев назад

how can i view the user management form? when I login as admin level, it only shows me 2 sheets. thanks a lot@@PKAnExcelExpert

@SharmaMusic-ui9bh 2 года назад

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

@merwanhajii5612 4 дня назад

I am from Ethiopia I'm very happy for this vedio I am not know before this vedio about VBA but after I finished the coding and other thing when I enter username and password say invalid password what's my error

@wojciechwisniewski2510 4 года назад

super video, dziękuję :)

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@hordaango9612 4 года назад

@@PKAnExcelExpert sir, can I have your E-mail Id please.

@nitinsingh.2107 4 года назад

Grt.....PK

@honestindian2106 4 года назад

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)

@emailtomayank 3 года назад

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

@DineshKumar-hz1dc 4 года назад

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

@riteshvishwakarma9595 4 года назад

Superb!

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@pauloferreira1590 4 года назад

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

@tajuddinidrisi6610 4 года назад

very nice , it very useful for me

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@belowaveragegirl8570 3 года назад

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? 🤔

@shivannahr780 4 года назад

Thank you sir

@PKAnExcelExpert 4 года назад

Thanks for watching

@rakibulhasan8592 4 года назад

Sir i have also seen yr inventory management and attendance management tuitorial, they are very nice, thanks u

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@rakibulhasan8592 4 года назад

@@PKAnExcelExpert please make dynamic balance sheet, ledger, profit loss Statement

@isaabdrahman Год назад

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

@BeerusHoldings 4 года назад

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?

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback. Yes we can do that. I will definitely try to make such

@BeerusHoldings 4 года назад

@@PKAnExcelExpert Thank you so much for sharing your knowledge with us :)

@ajayrathod7777 3 месяца назад

Can multiple people login in this excel file with different login ,when kept on shared drive and save work?

@emptymind9107 4 года назад

Thank you.♥️ I requesting you to make video on file transfer(copy.paste) from 1 folder to other multiple folder's..

@PKAnExcelExpert 4 года назад

Sure, I will definitely try to make such video

@emptymind9107 4 года назад

@@PKAnExcelExpert sir awaiting for video

@muditraniwala9405 4 года назад

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.

@eknarf9793 2 года назад

nice take care have a great day

@PKAnExcelExpert 2 года назад

Thanks

@kuldeepkumar0884 4 года назад

Very useful

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@nejatbocnak1699 3 года назад

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 ?

@LotsOfTrade 4 года назад

Brilliant.. u r talented

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@calvinhimanshu 3 года назад

@@PKAnExcelExpert you copy randy project from excel for freelancer

@jefferyashiboye3734 2 года назад

@@PKAnExcelExpert please I keep getting an error saying “method hidden of object range failed” help me please

@obedido123 3 года назад

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

@mauricechoo368 3 года назад

If the excel contain pivot table and the data are in table (control + T) , is it still able to coding the user management?

@J3rrycurl 7 месяцев назад

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?

@PKAnExcelExpert 7 месяцев назад

Please check the worksheet name is correct or not.

@venus_in_berlin 7 месяцев назад

I have the same issue at the same point in the code! Followed along step by step but got the run-time error 9

@dodigio 10 месяцев назад

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!

@venus_in_berlin 7 месяцев назад

Unfortunately doesn't get rid of the problem for me 😢

@inderr7bajaj 3 года назад

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?

@fouzanism 8 месяцев назад

Hi PK! Will the users have access like as shown in your video if I shared the workbook on Google sheets?

@reymarkgamil947 Год назад

Wow amazing

@PKAnExcelExpert Год назад

Thanks🙏

@hasanandmotwanin1643 4 года назад

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.

@swastikhotmail 4 года назад

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.

@MrBrianpark123 Год назад

Hi @swastikhotmail. Were you able to ever find a solution to the latter question? Coming across the same issue now. Thanks in advance!

@swastikhotmail Год назад

@@MrBrianpark123 Not yet

@jeffnoahmendoza2777 Год назад

@@MrBrianpark123 Hi, did you find the solution for this? Thank you

@MrBrianpark123 Год назад

@@jeffnoahmendoza2777 Unfortunately no

@ShaibulHoqueMozumder 4 года назад

than you sir

@PKAnExcelExpert 4 года назад

Welcome

@itsmesujoy9351 3 года назад

Do i have to save after writing every code? To see whether its working or not?

@MIMGURU 2 года назад

Fantastic

@PKAnExcelExpert 2 года назад

Thanks for your valuable feedback🙏

@bgovind9759 Год назад

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.

@1616ever 2 года назад

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 :)

@shilpareddy_thelady Год назад

i am also lookng for share point

@sonyrizwan5959 4 года назад

Can we make a condition that same workbook can be access by all users and allow to save changes at the same time

@EduFeastFamily 4 года назад

Thank you. Your download link is little bit difficult the practice file. May be change it for direct link.

@bogdanbogdan2943 4 года назад

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!

@karthikb.k2753 4 года назад

Hi Mr.Bogdan & Mr. PK, I wanted to ask same question. Request you to share your thoughts. This was very helpful and excellent work.

@OrvilleCota 3 года назад

VBA, for now, doesn't work in the browser. It works only if you first run it in a desktop app.

@smr8854 2 года назад

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.

@sathish7659 3 года назад

It's working for mr very nice

@PKAnExcelExpert 3 года назад

Thanks for your valuable feedback

@sathish7659 3 года назад

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

@pragyamittal3129 4 месяца назад

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.

@dowellto7115 4 года назад

Very nice sir Ek achchhi job k liye excel me kya. Kya sikhna chahiye

@nehapopli8803 Год назад

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.

@sinankhan4509 3 года назад

Can we put the file in sharing folder and enable multiple users?

@anandchaudhari901 4 года назад

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

@Munch858 3 года назад

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

@MrBrianpark123 Год назад

Hi @chihuahua7740. Did you ever find a solution to this? Coming across the same issue now. Thanks in advance!

@vishaldoshi6641 Год назад

@@MrBrianpark123 I am finding same problem. If we put this in shared drive it looses its capabilities

@hossammostafa649 2 месяца назад

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

@dhrubotara6890 2 года назад

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,,,

@dayanandjadhav1286 4 года назад

I have copy all code its working only for admin but User login error getting Set wsh = Thisworkbook.Sheets(sh.cells(2, i).value This line is highlighted yellow I dont get from where there i coming ?? Please help to resolve the issue

@toddtate3823 2 года назад

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?

@abdersoy1462 2 года назад

thanks Health to your language (Turkey)

@snjd962 5 месяцев назад

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

@afzalkhanmiefellow8661 4 года назад

v.good

@PKAnExcelExpert 4 года назад

Thanks for your valuable feedback

@bobbywibowo3654 2 года назад

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

@fiqah2907 3 года назад

Hi PK & everyone. can you kindly assist with the code below it shows error when i run the User role access . may god bless you all . please help and thanks Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value)

@yuuuhben8165 2 года назад

HELLO DID YOU FIND A SOLUTION?

@microsoftdestination6249 4 года назад

I cannot see your screen clearly on my mobile. You are genius in excel. I see after the macro using quality 480p

@Mdmanik-ie7xh 2 года назад

nice video

@PKAnExcelExpert 2 года назад

Thanks

@SaktishreeDM 4 года назад

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.

@kiranarana7274 4 года назад

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‘

@sanilalkuttimon1755 3 года назад

😍😍😍

@PKAnExcelExpert 3 года назад

Thanks

@kalyanchakravarthy8317 Год назад

Thankyou so much... please let us know how to lock the vba...

@PKAnExcelExpert Год назад

I will create a video on this topic

@abhisaardubey3762 4 года назад

@PK: An Excel Expert - Bro, everything working fine...but user management sheet is getting hidden even for admin....please help

@Chinhto0113 3 года назад

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!

@kevalrabadiya 9 месяцев назад

I am inspired from your video by seeing this and write code but some error occurred " run time error -13(type mismatch)"

@lissettegutierrez2876 Год назад

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

@PKAnExcelExpert Год назад

Yes, it is possible. I will try to add in next version.

@saiyfali4644 4 года назад

Sir Admin options is works But user options is not working . So how to rid of this ? And can you explain this please For i = 5 to application.worksheetfunction.countA(sh.range("2:2")) Set wsh = thisworkbook.sheets(sh.cells(2,i). value Its giving me error while i want to login as a user

@tonnyokwirspohi710 28 дней назад

Please help me identify error in these codes, it is showing runtime error especially that line for vlookup function.

@vijildevkvkv 2 года назад

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

@MrScottBruce Год назад

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?

@o0TraDa0o Год назад

did you solve this problem? i also got this error

@opentrainingcamp 2 года назад

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.

@user-ni8hy8is3c Год назад

Hello sir Pls Make Video About If we share workbook to someone he will be only one Person Access Edit and Modification , It should be open in only one system,. If in case he share to somebody it couldn't open ....How can we do this ....pls make one video Its very helpful...