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.
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!
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💯👍
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. 🙂
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.
This tutorial was awesome.I can say it was the most useful TH-cam video for me! I was searching for a solution for exactly the same requirements and you saved me. Thank you so much
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?
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.
This is really helpful. 1 question though, if this workbook is shared via sharedrive and multiple users open the file at the same time, they can see all the sheets that are open by other users?
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!
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.
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 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
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.
Hi, thank you very much for this amazing tutorial! I have a problem with code on CommandButton1, when I start VBA, I insert correct username&password in UserForm (Admin and admin), but VBA says me "Error run-time '10041": password isn't correct, check for BLOC MAIUSC etc" and I go only to UserManagement tab. When I do debug error, I have error on "wsh.Unprotect 123".. but where is the problem? :((( 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.
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 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
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
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?
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)
Hi! I would like to ask you that I want to set the user access to users security on the protected sheets where user will enter the data on highlighted fields. is this possible. please confirm.
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,,,
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!
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.
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 ?
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.
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
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 :(
Hi PK, Macro working fine till admin rights but at User rights getting error, For i = 5 To Application.WorksheetFunction.CountA(sh.Range("2:2")) Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value). If possible share test sheet.
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 :)
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 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)
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
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
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 ?
@@PKAnExcelExpert I uploaded a version of this to a sharepoint and when 2 users are logged in under their user name and password they can see all the users tabs even though I've restricted them under the user management tab. Can this not be used on a sharepoint?
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.
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
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'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
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
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 amazing - never did any VB programming in Excel but this is a great way to introduce it!
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!
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!
Fantastic, bro....
U explained it very well than others....
It is exactly what I need in my work...
Thanks....
Thanks for your valuable feedback
This tutorial was awesome.I can say it was the most useful TH-cam video for me! I was searching for a solution for exactly the same requirements and you saved me. Thank you so much
I am speechless my friend, excellent work
Thank you very much!
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
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
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🙏
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
Amazing Bro, really you are and expert.
Many many thanks
Thank you very much!!!
You're welcome!
Bahut hi lajawab sir
Thanks for your valuable feedback
Thanks for sharing this wonderful video 👍🙂
Thanks for your valuable feedback
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
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
This is really helpful. 1 question though, if this workbook is shared via sharedrive and multiple users open the file at the same time, they can see all the sheets that are open by other users?
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 😢
obrigado pela ajuda ....estava com mesmo erro e vc me ajudou
nice take care have a great day
Thanks
Thank you so much for this, very helpful. One quick question, would this work if saved in a shared folder?
Yes, it works.
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?
Hi PK! Will the users have access like as shown in your video if I shared the workbook on Google sheets?
very nice , it very useful for me
Thanks for your valuable feedback
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.
thanks !!! very good !!!!
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.
Thanks a lot buddy ! It helped :)
Thanks for your valuable feedback
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
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
thanks
Health to your language (Turkey)
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.
Hi, thank you very much for this amazing tutorial! I have a problem with code on CommandButton1, when I start VBA, I insert correct username&password in UserForm (Admin and admin), but VBA says me "Error run-time '10041": password isn't correct, check for BLOC MAIUSC etc" and I go only to UserManagement tab. When I do debug error, I have error on "wsh.Unprotect 123".. but where is the problem? :((( thanks
Wow amazing
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.
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.
you are amazing keep up the great work
Thanks for your valuable feedback
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
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?
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
Can multiple people login in this excel file with different login ,when kept on shared drive and save work?
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
Very useful
Thanks for your valuable feedback
nice video
Thanks
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
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
Fantastic
Thanks for your valuable feedback🙏
Superb!
Thanks for your valuable feedback
Thank you sir
Thanks for watching
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
Thankyou so much... please let us know how to lock the vba...
I will create a video on this topic
Grt.....PK
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)
Hi! I would like to ask you that I want to set the user access to users security on the protected sheets where user will enter the data on highlighted fields. is this possible. please confirm.
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 :)
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,,,
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
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.
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.
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 ?
Great content and demonstration,
How could we lock a specific user after 3rd attempt?
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?
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.
I am inspired from your video by seeing this and write code but some error occurred " run time error -13(type mismatch)"
Thank you. Your download link is little bit difficult the practice file. May be change it for direct link.
Can’t we use this type of setup in OneDrive online mode
Do i have to save after writing every code? To see whether its working or not?
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
Hi, would highly appreciate if you can let me know if you found a way to do this!
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.
super video, dziękuję :)
Thanks for your valuable feedback
@@PKAnExcelExpert sir, can I have your E-mail Id please.
I cannot see your screen clearly on my mobile.
You are genius in excel.
I see after the macro using quality 480p
if someone open the workbook and goes to the developer tab and open visual basic then how to protect them.
please reply sir.
When i open for first time it says *Enable Editing* and display all sheets, After enabling it ask for user name and password. how to solve this.
Before clicking Enable Editing it is easy to show all user names and passwords.
give password to VBA project
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 :(
Hi PK,
Macro working fine till admin rights but at User rights getting error, For i = 5 To Application.WorksheetFunction.CountA(sh.Range("2:2"))
Set wsh = ThisWorkbook.Sheets(sh.Cells(2, i).Value). If possible share test sheet.
Please download this excel file from the link given vedio's description box or visit our website Www.pk-anexcelexpert.com
@@PKAnExcelExpert : Appreciate your all teachings here. I am having same error while checking for users. Could you please post here the code for this.
ı have the same issue. how did u solve it ? thx.
Hi, I have the same error. how did you fix it?
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
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
Can we make a condition that same workbook can be access by all users and allow to save changes at the same time
than you sir
Welcome
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)
HELLO DID YOU FIND A SOLUTION?
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
Can you do this on Excel Online?
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
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.
When sharing the excel sheet. Will the same code work if multiple users are accessing it at the same time?
Yes, absolutely
@@PKAnExcelExpert I uploaded a version of this to a sharepoint and when 2 users are logged in under their user name and password they can see all the users tabs even though I've restricted them under the user management tab. Can this not be used on a sharepoint?
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.
Can we put the file in sharing folder and enable multiple users?
In this file how I can write a code to always open a sheet named homepage
I think you made a very nice presentation but I'm having issues when logging in as a user. It always says Runtime error 9 subscript out of range. Why?
I have exactly the same issue. And as I am just starting with Makros in general I can't find the issue 🤔
If the excel contain pivot table and the data are in table (control + T) , is it still able to coding the user management?
v.good
Thanks for your valuable feedback
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
Hi, got an answer to this?
It is better if you add user logout button so that user can login to other credentials without exiting workbook
I will try to add this in next version
Is it possible to add autofilter option in protected sheet.?
Yes. It is possible. I will try to create a video on this topic
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!
Please help me identify error in these codes, it is showing runtime error especially that line for vlookup function.