Thank you so much. I did not even know this was an option in Excel and has really highlighted how much there is to learn - I am definitely considering purchasing your course as your instructions and explanation were clear and concise. I only had one issue with this line of code: If .Cells(UserRow, SheetCol).Value = "Ð" Then - it kept coming up with an error but when I change the previous line from SheetNm = .Cells(4, SheetCol).Value 'SheetNm to SheetNm = .Cells(4, SheetCol).Value 'Sheet Name it fixed it! Thank you once again, it was brilliant :)
Hi and thanks so much for your feedback. I can understand your issue. I did this training many years ago, before I realized the "Ð" character would not work in every regional setting. Now instead, I will use the character equivalent which is Chr(208), so your line would be If .Cells(UserRow, SheetCol).Value = Chr(208) Then I hope this helps and thanks so much.
You are a very creative person! I myself have long drawn tables and even make programs, but when I saw your work-just opened my mouth in amazement! You don't need to draw tables, but cars!
Thank you so much. I really appreciate that. I see that you are "Accounting In Excel" so please check out lots of my videos in Accounting I think you will really love.
Another great video. I thought I was good at coding until now that through your videos I discover that there is so much you can do and at the same time simplify the codes. I still have a long way to go. btw I like your style of teaching, you are not too fast, the pace is perfect. Thank you for your dedication
I absolutely loved this tutorial and followed it all the way through to create the same product for a payroll template at work. I am so excited with the outcome and it works beautifully. TY for your videos. You're a great teacher!
Wow! Your lessons never cease to amaze me Randy! I learned lots of cool stuff from this lesson, not the least of which was the CELL function and how to lock up my VBA code. I am very grateful for your awesome teaching style!
Hi Randy, thank you so much for sharing your very clever approach on how to add user login and security rights to an Excel workbook. I came across your TH-cam video last week and liked it very much. I have downloaded the workbook and have made several changes for improvement. Thanks again for sharing. 👌👌👌 I now need to explore the best option to add the security to existing workbooks. Here is a list of the modifications made/planned: 1. Added a password reset form, where users can change their passwords. 2. Masked all passwords. 3. Changed the VBA to automatically remove the entry under 'All Sheets' when one of the worksheet protection options is changed and is no longer the same as the entry under 'All Sheets'. 4. Modified the original formulas to check for an exact match of the passwords, hence "Password123" in no longer seen as the same as "password123". 5. Expanded on the number of workbooks to be protected to 15 and changed the ranges referenced in the VBA to more dynamic ranges for flexibility in future expansions. 6. Added "Read-only" as a fourth worksheet protection option, visualized by an 'eye', represented by Webdings font letter N and expanded the conditional format to show this symbol in Blue. 7. I am contemplating to add an Excel table for an audit trail of user logins and password resets.
Those are some great ideas and for sure its going to be a powerful workbook. If you run into any issues with that feel free to post your questions in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Thanks so much.
I really appreciate the good quality and easy understanding help in your videos; especially I can use this tutorial about user login. It was quite easy to follow, and create a “personalized” file for our use. However, there is one thing that could be very cool to add, if possible. I’m thinking about a counter, that can tell which users have opened the file, and maybe when
Hi its a great point you make. I am thinking very soon to create a 'Change Log' Application which not only how which users have opened but what changes they have made in the workbook along with an 'Undo Changes' option which would allow a user to reverse the actions made. I think this would be helpful
Great tutorial, and fantastic ideas. I do have one very small suggestion for using the "BeforeDoubleClick" trigger. I like to add "Target.Offset(1).Select", or "Target.Offset(0, 1).Select" in the Sub, that way the cell you double-click does not become active, with the cursor flashing at the end of the character string. I personally find that annoying, so adding that simple line can help a lot. Thanks for the video.
HI and thanks yes that is a great suggestion. In my recent videos I have also added the selection of another cell when I use checkboxes or some single cell selection function. Thank you for your Likes, Shares & Comments. It really helps.
First of all I'd like to thank you for this amazing guide. Second, is it possible to allow a user to view one sheet but only be able to write (edit) certain rows?
Hello Johnny, Thanks for your comment. It would be possible just make sure those specific rows are not protected. If they are going to be visible, and protected for other users you could add/remove the protection on these specific cells via VBA. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
After 6 years it's still awesome... Absolutely love it.. i never thought that it could happen... I only have one question... If i need to share the file and make everything work on another device how can i do that ?
HI and thanks so much, I am glad you loved the video. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html
Thanks for the wonderful training. I keep getting an error code here and I've gone over and over it and I cannot find what I did wrong!For SheetCol = 7 To 41 SheetNm = .Cells(3, SheetCol).Value 'Sheet Name If .Cells(UserRow, SheetCol).Value = "Ð" Then Sheets(SheetNm).Unprotect "123" Sheets(SheetNm).Visible = xlSheetVisible End IfCan you help?
Hi thanks, you can move the ClearContents Line Below the UserRow line like this: LoginForm.Hide UserRow = .Range("B8").Value 'User Row .Range("B5,B6").ClearContents In the download sample I had provided this issue was corrected like the above. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
that was great! no doubt the way you deliver your presentation convinces the viewer from a skillful master. i actually learn a lot from your presentations.
Thanks so much Landge, I really appreciate that. Make sure you to catch my latest series "School Manager" in which I am creating an entire application from scratch in a multi-part series here: bit.ly/SchoolManagerSeries
Hi thanks for your message and sorry you are having issues. I have created a Facebook group with 20,000 Excel Experts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
@@ExcelForFreelancers It seems that dropdown lists are not working after used your perfect sharing. Do you have any kind solution to overcome the issue? I may do something wrong ! Thx.
HI and thanks, make sure you check the data validation and the named ranges are accurate. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers First of all thank you for the prompt return. I checked data validation and the name ranges but could not solve yet. joined the fcb group. and I wrote so. Thx.
Thank-you for this excellent tutorial. I am using Excel 2016, Windows 10, and I did have 2 problems with the code, just for your information, but I am pretty new to VBA. 1) Clearing both UserName & Password in the CheckUser() code caused Excel to crash/go into file repair and I had to completely rebuild my workbook. All my conditional formatting was lost, and my page layouts were scrambled, with lost data, etc. but the VBA code was still there for copying over. When I changed to clear password only all worked fine, plus I set the field property to "*" to hide the password when it is being entered. I liked having the last UserName left in the login screen. 2) The UserLogin screen did not open in a good position on my PC monitor (far left edge) so I added UserForm_Activate() to set .Top & .Left positions - would be nice to have included in your tutorial. I did manage to build in a 4th security level for sheets requiring Visible.xlSheetHidden, but it was pretty tricky, especially for the All Sheets code! You warned us the order of the code was crucial and you are so right! 3) Your formula to automatically update the worksheet name in Row 4 is a fantastic addition, but hard to see on the video. Thank-you so much for including though!! 4) I added a simple formula for a "Welcome Back [UserName] !" banner (Brush Script M7, Size 48) to the Start screen, just to make it a bit less sterile. Found out you have to be very careful which security selections you make, but overall excellent code! I am looking forward to seeing more of your work! Thank-you for sharing!
Hi Lyla, thanks so much for the comment and glad you got it working. I have also learned never to allow Excel to 'repair' my workbooks, because so much is lost. I keep all of my work in a Dropbox folder, which is a free program that allows you to store any version online. So if i need to revert back to a version 1 hour ago, 1 day or 1 week I can. Great update and idea on the user login. Also on forms I have learned not to use the 'Control Source' field. This field connect a form field to a cell is Excel. Normally this is fine, but in some versions it creates real problems and mapping issues and sometimes crashes. Instead when launching a form. I will just pull the value into the form or put the value into a cell without connection such as Sheet1.Range("A1").value = Loginform.Username.Value or LoginForm.Username.Value = Sheet1.Range("A1").value Also concerning the protection of passwords I have just taught a brand new way to protect passwords which makes it impossible to view any password without an admin password in the Accounting Application I am creating in our Excel For Freelancers Mentorship Program here: bit.ly/MentorshipProgram_YTCom I hope this helps and thanks so much for the comment and feedback.
@@ExcelForFreelancers Thanks for the quick reply, but I am confused because I am new to VBA and am just learning from online videos such as yours and have to modify your code for my workbook. Are you instructing me to delete the ControlSource properties for the textboxes named Usernamefld and Passwordfld and instead add either line of code? Which sub do I add it too, or do I need to create a new Private Sub? Without the ControlSource property, don't we need 2 lines of code to be added, one for Usernamefld and another for Passwordfld so B8 updates to the correct row to identify the security settings? My code did seem unstable, and I was afraid something was causing conflicts. I really appreciate your helping me improve this code! I am using this workbook in an application that needs confidentiality and this is the first time I have applied a login screen. I have built other workbooks that would benefit from having a Login too.
Hi thanks Lyla, the best way to move forward would be to take a basic VBA course. My videos all deal with Advanced VBA, so I really dont get into the basics. For specific excel-related questions you can also post them in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 23,000 members who would love to help you. Also you can locate the "Excel VBA Is Fun" TH-cam channel. This channel covers lots of VBA basics and would answer your questions above regarding VBA basics
@@ExcelForFreelancers Hi, I own the ebook Excel 2016 VBA and Macros by Bill Jelen, but I quickly found out that code if affected by bit level (I use Office Home & Student 2016 64-bit) and I encountered quite a few problems following some of the code in the book. It was pretty frustrating - do you have a book to recommend? Got it! I removed ControlSource and added 2 lines of code in the CheckUser module, and the code runs good. Thanks again for all your help!
Hi Lyla, I am glad you got it. I also try to write code that can work in either 32 or 64 bit versions by limiting functions to almost none. Thanks again for the feedback and questions.
Loved it. Changing it a bit to work with named ranges so it is more dynamic. So I can intergrate it into a huge excel system (Where we should use access for but that would mean me learning access without having the time for it ) Now just a way for live editing without having to manually sync. Co-authoring it will be I guess with editing via desktop app. Hope that works with a intergrated user/password system.
Great job and glad you enjoyed the video. You can set sync to run on any worksheet change or database change so that it runs in the background whenever users make changes or based on any time interval (In the School manager series I show you how to do this) I hope this helps and thanks so much.
Thank you so much! In BeforeDoubleClick add Cancel = True right after the function As Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True
Hi Thanks for the comment. Yes that would cancel the current cell, however it depends on what function you are trying to perform. With testing you can achieve the feature you want.
Amazing Boss for this User Login & Security Rights Training. I need one more thing in this sheet that if we share this sheet on drive with multiple user with user rights and at one time 2 or 3 user work on the sheet how they can access at same time and save their work. Thank you
Hi and thanks so much. You can have multiple people work on your macro-enabled workbook using the techniques I teach in this video: th-cam.com/video/aKV5seZmiBs/w-d-xo.html I hope this helps and thanks so much.
Great idea, and very informative. I also made it dynamic using a table and name ranges so that it would not crater (such as if a row were added midway through the hidden A:B data). However the greater need here is that it is totally possible for someone to accidently erase the Admin data, either by a delete key, or absent mindedly clicking that sheet so that nobody has edit access to it. This would make it impossible to edit the sheet, even if it were viewable. I did a few things to try to stop the errors, and then realized it could still occur beyond all efforts to avoid it. So to solve, I added two lines in the WBSecurity, so that if nobody is selected for rights to this sheet in the SecurityAdmin column, then it gives the right back to the Administrator's line, which I named "AdminAdmin". CkAdmin = Application.WorksheetFunction.CountIf(Range("SecurityAdmin"), "Ð") If CkAdmin = 0 Then .Range("AdminAdmin") = "Ð"
Thank you for your feedback! It's always great to hear that the video was informative and helpful. I appreciate you sharing your insights and suggestions on making the process more foolproof. Keep up the great work!
FYI.. my moment of insight is accidental discovery. I was in the process of trying it out and had clicked the ‘all sheets’ of one line making all ‘view only’. Haha. Oops. The saving grace was I had used viewable, and that some code malfunction near the end allowed me to use the Immediate windows to change the information in the necessary cell for another run of the login.
That's quite the accidental discovery! Sometimes the best ideas come from unexpected moments. Keep exploring and you never know what other insights you might stumble upon.
I used one trick to make clicks nicer (to avoid entering edit mode). I change the range to a cell, like E4 and next i Return to the same cell. PS: Very good tutorial as usually!
Good video. Unless I am doing something wrong, unfortunately I have found that caution is required if you intend to use the finished sheet on a shared drive (like One Drive). If I, as administrator, have the sheet open and someone else logs on they will see all the available tabs - they might not be able to open them but one of the points of this approach was that they wouldn't even know the tabs are there. In fact, if person A is logged on and person B opens the sheet then Person B will see Person A's tabs.
Hi Dave, thansk so much for your comment. To solve this issue you can use OneDrive to share and sync the data only and not the entire workbook. Each user would have their own, unique workbook, and only the data, one cell at a time, is shared and synced. I show you how to do this in this video: Ith-cam.com/video/aKV5seZmiBs/w-d-xo.html I hope this helps and thanks so much.
one of the best tutorial i have seen and learn something new thanks for sharing your knowledge i have also implemented your this idea in my work and its really worked and helped me
someone probably pointed this out already but Dim UserRow, SheetCol as Long results in UserRow being a variant data type. you have to explicitly assign a type to each variable. Dim UserRow as Long, SheetCol as Long
Hi Jonathan, yes and thanks. All of my recent videos for the last year have them Dim'd specifically for each one instead of a general. (See some of my more recent videos to see the updates) Thanks so much for the feedback.
Finally at the end ! But I have a new question. How about it MACRO over Users and LoginForm? Your comments at the end the code on load WorkBook Open. Any continuos video at this point? The action button not make a capture data from LoginForm to puttin in the respective cells. Then usign de rest of macro code to apply.
Hi and thanks so much. I am glad you enjoyed the video. I show you how to do that in this video: th-cam.com/video/o1KdIaug6ac/w-d-xo.html I hope this helps and thanks so much.
Hello, thanks for work.. it's amazing Correct me if I am wrong, it is supposed that B5 receive the user name from the login panel and same for B6.. but it seems in your work that the login panel is receiving the content of B6 and B5
Hi and thanks for your comment. This training was a few years ago so I am not sure on the cell assignments. Can you share your code in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 50,000 Members who would love to help you with this. Thanks so much.
very nice tuto (as usual) i love the way you used Windingsfont, makes life so easy :) The tip about the VBA password came to late for one of my projects, but then again, learning form own mistakes is a great teacher too :D
Great coding tutorial. I enjoy watching. As programmer point of view, I would be much better if this code follow coding standard and structure. Thanks for sharing.
Thank you for this, wondering how to solve for document being located on SharePoint / Teams site and multiple individuals signing in at the same time? It appears to overwrites the other credentials.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/G9VqF7yN0Oo/w-d-xo.html I hope this helps and thanks so much.
Hi and thanks very much. It should work in OneDrive, but not sure about Sharepoint. Please test this with your own settings to see which one is fastest and works best
This is a great video. Could you tell me how to do the double click and select the entire column instead of the row so that all users could see a certain page, much like what you have in your first column, just working the other way? For example, if you double click G5 it will change from G5 to G26, or H5 will change H5 to H26, so on and so forth.
Hi and thanks you can use the Before Double Click event on any worksheet, then, within that event you can use something like Range(Cells(5,Target.Column),Cells(26,Target.Column)).Select Something like that will work. I hope this helps and thanks so much.
HI and thanks so much Pete. I created a specific training on how to share and sync your macro-enabled workbook here: th-cam.com/video/aKV5seZmiBs/w-d-xo.html I hope this helps and thanks so much.
Hi Randy, thank you so much for your wonderful presentation. This is what I was looking for all my entire life. you are such a good teacher. I have been using excel but didn't know that Visual basic existed. Therefore I decided to follow your tutorial step by step, but my challenge is that I have done all the coding the way you did and successfully run most codes in the security work book but when I run it, I get a message box saying "Run time error '424' Object required", and when I click on "debug", it highlights a line in the code that says " If.cell(userrow.sheetcol.value = "D" Then". What can I do? I am on the last stages of completing the admin sheet
Hi and thanks so much. I am glad you enjoyed the training. When you have that type of bug, that means your language pack does not recognize that character, so instead of that character with the quotes, you can use the character equivalent such as chr(208) instead of "D" You can find these numbers when you click Insert > Symbols, then use the Webdings Font and look for the character # in the lower right I hope this helps and thanks so much.
Hi Randy, Thank you very much for this great video. I have a question. How to make the form appear when user opens the workbook through a web link (through sharePoint). Thanks
Hi and thanks so much. Inside the VBA Editor, check the Workbook module which has a Workbook_Open event, you can display a userform on that workbook open event. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Dear Randy Thank you so so much for great tutorial! Is is possible if when open file, I want to show not only sheet "Start" but also some another sheet?
Hi and thanks for your comment. You can show any sheet on start when you either activate that sheet on Workbook Close event or when you activate the sheet on the Workbook Open event. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so much for the amazing video! Im facing some issues while the code for unprotect “shows script out of range” Any help would be highly apprised.
Hi Udit, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Hi Randy, Thanks for sharing much knowledgeable videos i really appreciate your hard work. I want to know does this file work in Microsoft 365 co-authoring, like on onedrive sharing any of my user can login with there id and it will store the data in data but each user ha different sheet in front of him and rest is very hidden sheet.
Hi and thanks so much. I have just uploaded a training on this. You can share and sync your macro enabled workbook with any desktop version of Excel using a shared folder such as Dropbox, OneDrive or Google Drive. I have a few videos on this, the latest is here To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html
Thank you for making your knowledge available I would like to ask a question. in this workbook if you have a user I have to hide a column. how to proceed.
Hi thanks you can hide any column based on a specific user, perhaps something like this. If username = "testuser" then sheet1.Range("A1").entirecolumn.hidden = True. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
This tutorial is good. Nevertheless two things have been left out. First, Admin to monitor logins and second restrict users based on number of times they have to login (with Date and time). If there could be an update to this video, it will be the best on youtube ratings on Excel VBA..
Hi thanks very much for your comments. There are probably 100 features i wanted to add, or could have added, however I had to limit the training a bit. Concerning the Login Dates & Times, I have this in both Change Long 1 & 2 videos here: Change Log 1: th-cam.com/video/6TnxPvLz_44/w-d-xo.html Change Log 2: th-cam.com/video/fE15W72vnEE/w-d-xo.html
HEY this was a perfect video i followed it and it worked great now i want to share the link of this workbook but the vba restrictions doesnt seem to work on there side how do i address this please?
HI and thanks so much. Anytime you download a workbook from the internet, the end user must first right click the file, go to properties and click the 'Unblock' macros checkbox before opening it. As an extra security measure, Microsoft disables macros from workbooks downloaded over the internet. You can fix this once and for all by following the steps I have outlined on this page www.excelforfreelancers.com/how-to-fix-macro-blocking-issue/ I hope this helps and thanks so much.
Yes for sure it can. I have a very specific video on that here, that allows you to share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html I hope this helps and thanks so much.
love your videos! i think if all pages are unlocked and you edit a specific sheet may it be locked or viewable only, the all pages access icon should be removed.
Dear Mr. Austin, I have just follow your Tutorial about "User Security Rights" . Thank you verry verry much. The Tutorial was verry clear and professional. I have learned a great deal in the past period just by following you and and your lessons. I was able to raise my excel workinglevel to a higher stage.
I am currently working on an Excel programm where I am dealing with different users with different loginlevels. How can I get a copy of your Excel script. I was able to follow you until you reached the Visual Basic part. Can you send a copy of this excelfile? . Thanks in advance 1000Kx Cancy
HI Cancy, thanks so much for your comment and I am really glad you enjoyed the video. Every video I do on this channel has a Free Workbook. You would just have to click the link sin the video description to get the workbook. Thanks again.
Hey, awesome tutorial! I have done everything and it's working great. HOWEVER, is there a way to set the active worksheet? so after login, users will move away from the "Start" page automatically to a different worksheet of my choosing??
O wait I just figured it out by adding "Worksheets("Sheetname").Activate" after the "B5,B6" clearance bit which I moved after the sheet protections bit :D everything working like a charm! thanks
Yes great idea. In the Admin screen you can add a drop down list of sheet names in which the user can select as their personal 'start page' you can then set this sheet to activate in VBA on startup. I hope this helps and thanks so much.
Hi Randy, setting up a Performance dashboard for a number of social care teams. That was the easy bit, however, Area managers do not teams to be able to look at each other's stats so when I came across this I thought WOW. And I was correct does the job perfectly, however, downloaded the workbook which allows me to add additional users but. Cannot add columns to the Admin sheet. I have tried everything, from adding to the conditional formatting, extending the column & row ranges in the vba but does not recognise they are there? It will do the fill all facility but the 3 stages of vision even though applied do not work , can you advise? Otherwise it is a great piece of work that is ideal for my needs
HI thanks Neil, You can remove the formula that displays the sheet names, and just add your own sheet names to the sheet name row. Also update the code accordingly to accommodate your sheet. If you need help with this please feel free to create a post in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel If it is a lot of work, as opposed to a simple question, please mark this post with PAID JOB. I hope this helps and thanks again.
Good explanation 👍👍, Good visual presentation 👍👍 and im very thankful for this tutorial i mange to put this on my project. I hope more tutorial will post that may help others ... Ps.. is there's a way to hide like this *** on the password when login..? Hoping for your response ☺️☺️ And thank you in advance ☺️☺️☺️❤️❤️
Hi, thank you very much for the tutorial! I have a problem with file downloaded, when I login (for example Admin - admin) I have VBA error runtin 9 with debug on Sheets(SheetNm).Unprotect "123", how can to fix it? thank you very much
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hey Randy I need your help! First of all thanks for the great video, I replicated the macro successfully but now I need help knowing how can i have multiple users edit and work on the macro at the same time. I'm using it so they can upload their weekly performance and restricting their access to other tabs to avoid data loss/modifications, i uploaded the macro in onedrive and they open the macro through their desktop app, but when someone closes or saves the macro it will close it for other users, and I don't know how to prevent it from doing that. Thanks for your time and hopefully your read my comment.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/aKV5seZmiBs/w-d-xo.html The idea is that each user has their own Excel application (not the same one). Only the data is shared about users via a shared folder such as OneDrive or Dropbox I hope this helps and thanks so much.
@@ExcelForFreelancers Thanks Randy! I had originally created the user login from this video, should I change it to the one in the new video? or is there an specific point from the other training i can pick up from to merge both contents or avoid modifying too much?
Hi Thank you for nice tutorial. I was able to implement, however while using with multiple users, workbook closes even when single user close out his / her worksheet Please advise how to get rid of the above issue
Hi and thanks for your message. You can shar and sync your macro-enabled workbook using this training here: th-cam.com/video/aKV5seZmiBs/w-d-xo.html I hope this helps and thanks so much.
@@ExcelForFreelancers I have a question in worksheet (1) in the data and I want to delete the repeated data in the rest of the other work papers by vba code How do I do this?
Hi thanks you can use a few different filtering techniques including advanced filter. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
Thanks Randy for the great video. If you don't mind me asking, following this trick it would add different users to the same excel with the same data? What if the excel is being sold to different users/companies Is it still possible to protect it from being re-sold by the users?
Hi and thanks, yes certainly you can protect it from being resold. I have a separate video on that here: th-cam.com/video/l-tgyQvdC5g/w-d-xo.html I hope this helps and thanks so much.
Thank you so much Randy for this tutorial. Please can you make security features for a file to become undeletable i.e user unable to delete or kill excel file or any other file? Thank you once more as we keep learning from you.
Hi Joe, thanks very much for your comment and glad you like the videos. your idea of making a file unable to be deleted would have to do more with computer and user rights then Excel, since only file structure rights of a computer can protect files. Using a backup folder such as DropBox ensures that there is always a backup copy of an application should the file be deleted. Thanks so much.
First, amazing videos. Thanks for sharing your knowledge and experience with the rest of us. My question is, after setting up the various rights to view/modify the spreadsheet, can it be used as a shared spreadsheet - several simultaneous users - or can it only be used by one person at a time? Thanks again for all of your videos and I look forward to your reply.
I'm not sure if you're still following these comments but I was also wondering about combining this spreadsheet, which grants different types of privileges to a workbook, and the other video where you show how to make a workbook accessible to several users simultaneously. It seems like they would be a natural fit but didn't know if you might have a template for that. Also, you used an example of a contact list which has mainly unique entires. How could you change to include repeated entries like a purchase log where the same companies may make numerous requests. Thanks! And again, your videos are awesome.
Thank you for this video, I find it very useful and hopefully I can apply it in my work. However, after minute 37, I got stuck. I receive an error message: Next without For. I have check several times and my code looks just the same as yours in the video. I don't know what could be wrong. Can you please help?
Hi and thanks you my want to check that any IF Statements within your For/Next loop also has an End If inside that loop I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi sir Undoubtedly It's amazing one. Is it possible to use this work sheet by multiple user at a time if we give access to each user only dedicated sheet or by using options like workbook sharing
Hi and thanks for your comment. Yes you can certainly. You can follow the training in this video. th-cam.com/video/aKV5seZmiBs/w-d-xo.html I hope this helps and thanks so much.
@@ExcelForFreelancers Dear Sir, Thanks for swift response. Video which you have shared is related to usage of separate workbook by different user & then finally synchronized. My requirement is usage of same excel workbook by same time by multiusers with their individual logins. With your access rights worksheet, when I used share workbook option, it is becoming single user. Please help.
your tutorial is fantastic, I really love the script you show which I would like to apply it in my job. Not sure if you could share the script to me so I can learn and digest it more effectively? Thanks a lot in advance.
Hi Kelvin Thanks for your comment. Every one of my TH-cam training contains a free workbook. You can access this workbook from the links in the description.
Hi and thanks, i use a formula for that, however you can simply type in the sheet name as that is a lot easier. If you would like to see the formula, feel free to download the workbook for free using the links in the description. I hope this helps and thanks so much.
Hello. Very detailed tutorial, many thanks!. Just wondered if you are able to do one that uses users usernames on a network, so that there is no need for the workbook to request for logon details, but will just open but display/restrict worksheets according to the rights assigned to that particular username?
Hi Rab, thanks for your comment, yes you can share any data on a network as long as that data is synced to the local workbook. I show you how to do that in this video: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
hello, this has been very helpful - is there any updated version. each time I try it I encounter a few errors, not sure it's me or if there are excel updates that dont work with this?
Hi and thanks so much. Instead of using the formulas to get the sheet names, just type in the sheet names and that should help. I am thinking about making an update to this, as you have a great idea since its been so many years since i did this. You can post your specific issue in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hello Randy! Thank you for your useful tutorial! I have one problem with the login page that everything want well and working perfectly at first. But, when I done the code, I save and close the workbook. After reopen the workbook, the Login form was not coming up? Please help me out for the solutions or any code was missing? Thank you in advance.
Hi and thanks make sure the code is in the workbook_Open event and that macros are enabled. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you for your useful video, Could you please help why error shown as " Run time error 9 Subscript out of range ( Sheets(SheetNm).Unprotect "123" )
Hi Ahmed, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
This is wonderful. I just have a quick questions regarding userform for passwords. Suppose whole team need to use the same sheet for some purpose and We create this macro so that everyone will not able to see what other data we have on different sheet. How can one person ( admin) controls that? As when the sheet opens anyone can see the admin page and update the restrictions/security as per there need. Is there any way that if admin uses his password then only he able to see the admin page and for other no admin page is visible until they login with correct password? Not even veryhiddensheet way you told in this video.
Hi and thanks very much, and I am glad you liked the training. The Best way to do that is on close, to show a start screen (for all users) then on startup the pages are displayed/hidden based on the user type entered. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html I hope this helps and thanks so much.
@@ExcelForFreelancers Thanks for replying. I am looking for a vba macro which works like a workflow. Which has the ability to assign task from one person to another in workbook, charts and data representation userforms. Which is accessible by all so that every employee can see what task has been assigned to him/her today. Could you please guide me to the collection of videos I can refer for this?
HI and thanks so much. I think my Kanban board will solve that issue here: th-cam.com/video/3qbU5S8ZaEA/w-d-xo.html If you combine that with the co-authoring link above you will have what you need. I hope this helps and thanks so much.
Hi thanks so much for the feedback. Make sure you also download the workbook from the links above. The formula to get the sheet names: =IFERROR(MID(CELL("filename",Sheet3!$A$1),FIND("]",CELL("filename",Sheet3!$A$1))+1,255),"") The 'filename' for any specific cell pulls the entire filename of the application including the path, workbook name and sheet name such as: C:\Users\Fred\Documents\User_Login_Security_Rights-2\[User_Login_Security_Rights.xlsm]Sheet3 But we only need the sheet name. So the formula uses "Find" to find the "]" and then returns all of the characters after this character, with a maximum of 255 characters, which is the actual sheet name I hope this helps.
Yes sure you can protect any range of cells you want with the Cell Protection property and make sure the sheet is also protected with a password. I hope this helps and thanks so much.
😱 𝗨𝗡𝗕𝗘𝗟𝗜𝗘𝗩𝗔𝗕𝗟𝗘 𝟲𝟱% 𝗢𝗙𝗙 𝗧𝗛𝗜𝗦 𝗕𝗟𝗔𝗖𝗞 𝗙𝗥𝗜𝗗𝗔𝗬! 👉 www.excelforfreelancers.com/BFSale2024_YTPinnedComm
Thank you so much. I did not even know this was an option in Excel and has really highlighted how much there is to learn - I am definitely considering purchasing your course as your instructions and explanation were clear and concise. I only had one issue with this line of code:
If .Cells(UserRow, SheetCol).Value = "Ð" Then - it kept coming up with an error but when I change the previous line from SheetNm = .Cells(4, SheetCol).Value 'SheetNm to SheetNm = .Cells(4, SheetCol).Value 'Sheet Name it fixed it!
Thank you once again, it was brilliant :)
Hi and thanks so much for your feedback. I can understand your issue. I did this training many years ago, before I realized the "Ð" character would not work in every regional setting. Now instead, I will use the character equivalent which is Chr(208), so your line would be
If .Cells(UserRow, SheetCol).Value = Chr(208) Then
I hope this helps and thanks so much.
You are a very creative person! I myself have long drawn tables and even make programs, but when I saw your work-just opened my mouth in amazement! You don't need to draw tables, but cars!
Thank you so much. I really appreciate that. I see that you are "Accounting In Excel" so please check out lots of my videos in Accounting I think you will really love.
Another great video. I thought I was good at coding until now that through your videos I discover that there is so much you can do and at the same time simplify the codes. I still have a long way to go. btw I like your style of teaching, you are not too fast, the pace is perfect. Thank you for your dedication
Thanks so much, I am really glad to share some new ideas and new ways of looking at Excel. Thank you.
Someone from India tried to recreate this without credit.
Follows will follow, leader will lead... You're very right sir.
Yes i saw that and TH-cam usually removes them. Thank you for your comment and I appreciate that.
I absolutely loved this tutorial and followed it all the way through to create the same product for a payroll template at work. I am so excited with the outcome and it works beautifully. TY for your videos. You're a great teacher!
You are very welcome Monica. Thanks so much for your comment. I am glad I can help and share.
Wow! Your lessons never cease to amaze me Randy! I learned lots of cool stuff from this lesson, not the least of which was the CELL function and how to lock up my VBA code. I am very grateful for your awesome teaching style!
Thank you so very much, I really appreciate that Rob. Glad you liked the training.
Super cool tutorial, thank you very much. The steps are well explained and the context you provide in your commentary is gold.
Thanks so much Toby, I am really glad you enjoyed the trainnig.
Hi Randy, thank you so much for sharing your very clever approach on how to add user login and security rights to an Excel workbook. I came across your TH-cam video last week and liked it very much. I have downloaded the workbook and have made several changes for improvement. Thanks again for sharing. 👌👌👌
I now need to explore the best option to add the security to existing workbooks.
Here is a list of the modifications made/planned:
1. Added a password reset form, where users can change their passwords.
2. Masked all passwords.
3. Changed the VBA to automatically remove the entry under 'All Sheets' when one of the worksheet protection options is changed and is no longer the same as the entry under 'All Sheets'.
4. Modified the original formulas to check for an exact match of the passwords, hence "Password123" in no longer seen as the same as "password123".
5. Expanded on the number of workbooks to be protected to 15 and changed the ranges referenced in the VBA to more dynamic ranges for flexibility in future expansions.
6. Added "Read-only" as a fourth worksheet protection option, visualized by an 'eye', represented by Webdings font letter N and expanded the conditional format to show this symbol in Blue.
7. I am contemplating to add an Excel table for an audit trail of user logins and password resets.
Those are some great ideas and for sure its going to be a powerful workbook. If you run into any issues with that feel free to post your questions in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 50,000 Members who would love to help you with this. Thanks so much.
Hi Herbert
@Herbertbeukenboom8546 Please share your updated template for our benefit.
Thank you.
I really appreciate the good quality and easy understanding help in your videos; especially I can use this tutorial about user login.
It was quite easy to follow, and create a “personalized” file for our use.
However, there is one thing that could be very cool to add, if possible.
I’m thinking about a counter, that can tell which users have opened the file, and maybe when
Hi its a great point you make. I am thinking very soon to create a 'Change Log' Application which not only how which users have opened but what changes they have made in the workbook along with an 'Undo Changes' option which would allow a user to reverse the actions made. I think this would be helpful
@@ExcelForFreelancers That would be very much appreciate! Thank you
Hi did you implement the change log? Thanks so much for sharing your great videos.
Great tutorial, and fantastic ideas. I do have one very small suggestion for using the "BeforeDoubleClick" trigger. I like to add "Target.Offset(1).Select", or "Target.Offset(0, 1).Select" in the Sub, that way the cell you double-click does not become active, with the cursor flashing at the end of the character string. I personally find that annoying, so adding that simple line can help a lot. Thanks for the video.
HI and thanks yes that is a great suggestion. In my recent videos I have also added the selection of another cell when I use checkboxes or some single cell selection function.
Thank you for your Likes, Shares & Comments. It really helps.
First of all I'd like to thank you for this amazing guide.
Second, is it possible to allow a user to view one sheet but only be able to write (edit) certain rows?
Hello Johnny, Thanks for your comment. It would be possible just make sure those specific rows are not protected. If they are going to be visible, and protected for other users you could add/remove the protection on these specific cells via VBA. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
After 6 years it's still awesome... Absolutely love it.. i never thought that it could happen...
I only have one question... If i need to share the file and make everything work on another device how can i do that ?
HI and thanks so much, I am glad you loved the video. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html
The "veryHidden" option was very hidden actually. Never knew it existed at all. Thanks Randy :)
For sure, you are very welcome Ganesh, I am happy I could help
You are an awesome guy, I have learnt a lot from you. You resolve every problem of mine related to excel.
Thank you so much Shashank, I really do appreciate that.
Thanks for the wonderful training. I keep getting an error code here and I've gone over and over it and I cannot find what I did wrong!For SheetCol = 7 To 41
SheetNm = .Cells(3, SheetCol).Value 'Sheet Name
If .Cells(UserRow, SheetCol).Value = "Ð" Then
Sheets(SheetNm).Unprotect "123"
Sheets(SheetNm).Visible = xlSheetVisible
End IfCan you help?
Hi thanks, you can move the ClearContents Line Below the UserRow line like this:
LoginForm.Hide
UserRow = .Range("B8").Value 'User Row
.Range("B5,B6").ClearContents
In the download sample I had provided this issue was corrected like the above.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question with code screenshots here:bit.ly/groupexcel
Thank you so much! That worked perfectly! I'm very excited to use this for my team!
Great I am happy to help
Hey April, did you find the solution? I followed every step and checked the code for three times, but still the same problem. I am exhausted
@@vickyli3187 HI Vicky, please use Chr(208) instead of "Ð" inside the code. I hope this helps and thanks so much.
that was great! no doubt the way you deliver your presentation convinces the viewer from a skillful master. i actually learn a lot from your presentations.
Thanks so much Landge, I really appreciate that. Make sure you to catch my latest series "School Manager" in which I am creating an entire application from scratch in a multi-part series here: bit.ly/SchoolManagerSeries
Debug occurring: If .Cells(UserRow.SheetCol).Value = "Ð" Then
can you please share the e-mail id so that I can forward you the attachment sir
Hi thanks for your message and sorry you are having issues. I have created a Facebook group with 20,000 Excel Experts so that many of us can help you with your Excel questions. Can you please post your detailed question, with samples or screenshots directly in our group? If you have not Joined yet, please join here: bit.ly/groupexcel. This way we can get you the answers you need, fast.
Same here. Did you get a solution? I think I have fixed it!
@@SimonsKitchenUK hello. ı am having same problem. how did u solve it ? thx.
The BEST Excel VBA tutorial in TH-camr ever! Thanks a lot!
Thank you so much. I really appreciate that Binye. I look forward to creating a lot more videos and training.
all thumbs up from the start to the GREATEStT VBA teacher Randy
Thank you so very much, I really appreciate that
Great man. You are an Uppercut in Knowledge and more above you are sharing knowledge to all.. Best Luck for next Tutorial.
Thank you very much Jayateerth, I am really glad you liked it.
I can not tell you my appreciation with words. All the best. Thank you very much.
Thank you so very much, I really appreciate that Nejat
@@ExcelForFreelancers It seems that dropdown lists are not working after used your perfect sharing. Do you have any kind solution to overcome the issue? I may do something wrong ! Thx.
HI and thanks, make sure you check the data validation and the named ranges are accurate. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
@@ExcelForFreelancers First of all thank you for the prompt return. I checked data validation and the name ranges but could not solve yet. joined the fcb group. and I wrote so. Thx.
@@ExcelForFreelancers ı wrote my issue fcb group, but no return so far. do you have any other solution for me? thx. a lot.
Allow me to thank you wholeheartedly for this very special and wonderful lesson.
For sure, you are very welcome and thanks.
You sir, are a genus. Born to instruct. Respect.!!
Thank you so very much, I really appreciate that
Thank-you for this excellent tutorial. I am using Excel 2016, Windows 10, and I did have 2 problems with the code, just for your information, but I am pretty new to VBA.
1) Clearing both UserName & Password in the CheckUser() code caused Excel to crash/go into file repair and I had to completely rebuild my workbook. All my conditional formatting was lost, and my page layouts were scrambled, with lost data, etc. but the VBA code was still there for copying over. When I changed to clear password only all worked fine, plus I set the field property to "*" to hide the password when it is being entered. I liked having the last UserName left in the login screen.
2) The UserLogin screen did not open in a good position on my PC monitor (far left edge) so I added UserForm_Activate() to set .Top & .Left positions - would be nice to have included in your tutorial. I did manage to build in a 4th security level for sheets requiring Visible.xlSheetHidden, but it was pretty tricky, especially for the All Sheets code! You warned us the order of the code was crucial and you are so right!
3) Your formula to automatically update the worksheet name in Row 4 is a fantastic addition, but hard to see on the video. Thank-you so much for including though!!
4) I added a simple formula for a "Welcome Back [UserName] !" banner (Brush Script M7, Size 48) to the Start screen, just to make it a bit less sterile.
Found out you have to be very careful which security selections you make, but overall excellent code! I am looking forward to seeing more of your work! Thank-you for sharing!
Hi Lyla, thanks so much for the comment and glad you got it working. I have also learned never to allow Excel to 'repair' my workbooks, because so much is lost. I keep all of my work in a Dropbox folder, which is a free program that allows you to store any version online. So if i need to revert back to a version 1 hour ago, 1 day or 1 week I can.
Great update and idea on the user login. Also on forms I have learned not to use the 'Control Source' field. This field connect a form field to a cell is Excel. Normally this is fine, but in some versions it creates real problems and mapping issues and sometimes crashes. Instead when launching a form. I will just pull the value into the form or put the value into a cell without connection such as
Sheet1.Range("A1").value = Loginform.Username.Value
or
LoginForm.Username.Value = Sheet1.Range("A1").value
Also concerning the protection of passwords I have just taught a brand new way to protect passwords which makes it impossible to view any password without an admin password in the Accounting Application I am creating in our Excel For Freelancers Mentorship Program here: bit.ly/MentorshipProgram_YTCom
I hope this helps and thanks so much for the comment and feedback.
@@ExcelForFreelancers
Thanks for the quick reply, but I am confused because I am new to VBA and am just learning from online videos such as yours and have to modify your code for my workbook.
Are you instructing me to delete the ControlSource properties for the textboxes named Usernamefld and Passwordfld and instead add either line of code?
Which sub do I add it too, or do I need to create a new Private Sub?
Without the ControlSource property, don't we need 2 lines of code to be added, one for Usernamefld and another for Passwordfld so B8 updates to the correct row to identify the security settings?
My code did seem unstable, and I was afraid something was causing conflicts. I really appreciate your helping me improve this code! I am using this workbook in an application that needs confidentiality and this is the first time I have applied a login screen. I have built other workbooks that would benefit from having a Login too.
Hi thanks Lyla, the best way to move forward would be to take a basic VBA course. My videos all deal with Advanced VBA, so I really dont get into the basics.
For specific excel-related questions you can also post them in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 23,000 members who would love to help you.
Also you can locate the "Excel VBA Is Fun" TH-cam channel. This channel covers lots of VBA basics and would answer your questions above regarding VBA basics
@@ExcelForFreelancers Hi, I own the ebook Excel 2016 VBA and Macros by Bill Jelen, but I quickly found out that code if affected by bit level (I use Office Home & Student 2016 64-bit) and I encountered quite a few problems following some of the code in the book. It was pretty frustrating - do you have a book to recommend?
Got it! I removed ControlSource and added 2 lines of code in the CheckUser module, and the code runs good. Thanks again for all your help!
Hi Lyla, I am glad you got it. I also try to write code that can work in either 32 or 64 bit versions by limiting functions to almost none. Thanks again for the feedback and questions.
Loved it.
Changing it a bit to work with named ranges so it is more dynamic.
So I can intergrate it into a huge excel system (Where we should use access for but that would mean me learning access without having the time for it )
Now just a way for live editing without having to manually sync.
Co-authoring it will be I guess with editing via desktop app. Hope that works with a intergrated user/password system.
Great job and glad you enjoyed the video. You can set sync to run on any worksheet change or database change so that it runs in the background whenever users make changes or based on any time interval (In the School manager series I show you how to do this)
I hope this helps and thanks so much.
Thank you so much!
In BeforeDoubleClick add Cancel = True right after the function
As
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Hi Thanks for the comment. Yes that would cancel the current cell, however it depends on what function you are trying to perform. With testing you can achieve the feature you want.
Amazing Boss for this User Login & Security Rights Training. I need one more thing in this sheet that if we share this sheet on drive with multiple user with user rights and at one time 2 or 3 user work on the sheet how they can access at same time and save their work.
Thank you
Hi and thanks so much. You can have multiple people work on your macro-enabled workbook using the techniques I teach in this video: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
I hope this helps and thanks so much.
Great idea, and very informative. I also made it dynamic using a table and name ranges so that it would not crater (such as if a row were added midway through the hidden A:B data). However the greater need here is that it is totally possible for someone to accidently erase the Admin data, either by a delete key, or absent mindedly clicking that sheet so that nobody has edit access to it. This would make it impossible to edit the sheet, even if it were viewable. I did a few things to try to stop the errors, and then realized it could still occur beyond all efforts to avoid it. So to solve, I added two lines in the WBSecurity, so that if nobody is selected for rights to this sheet in the SecurityAdmin column, then it gives the right back to the Administrator's line, which I named "AdminAdmin".
CkAdmin = Application.WorksheetFunction.CountIf(Range("SecurityAdmin"), "Ð")
If CkAdmin = 0 Then .Range("AdminAdmin") = "Ð"
Thank you for your feedback! It's always great to hear that the video was informative and helpful. I appreciate you sharing your insights and suggestions on making the process more foolproof. Keep up the great work!
FYI.. my moment of insight is accidental discovery. I was in the process of trying it out and had clicked the ‘all sheets’ of one line making all ‘view only’. Haha. Oops. The saving grace was I had used viewable, and that some code malfunction near the end allowed me to use the Immediate windows to change the information in the necessary cell for another run of the login.
That's quite the accidental discovery! Sometimes the best ideas come from unexpected moments. Keep exploring and you never know what other insights you might stumble upon.
I used one trick to make clicks nicer (to avoid entering edit mode). I change the range to a cell, like E4 and next i Return to the same cell. PS: Very good tutorial as usually!
Hi that's a great idea. good going, and thanks very much for the compliment. Glad you enjoy the videos.
Hi how can i add this
Really great tutorial. Thank you very much for the video.
Thank you so very much, I really appreciate that Alyssa. Thank you for your Likes, Shares & Comments. It really helps.
Good video. Unless I am doing something wrong, unfortunately I have found that caution is required if you intend to use the finished sheet on a shared drive (like One Drive). If I, as administrator, have the sheet open and someone else logs on they will see all the available tabs - they might not be able to open them but one of the points of this approach was that they wouldn't even know the tabs are there. In fact, if person A is logged on and person B opens the sheet then Person B will see Person A's tabs.
Hi Dave, thansk so much for your comment. To solve this issue you can use OneDrive to share and sync the data only and not the entire workbook. Each user would have their own, unique workbook, and only the data, one cell at a time, is shared and synced. I show you how to do this in this video: Ith-cam.com/video/aKV5seZmiBs/w-d-xo.html
I hope this helps and thanks so much.
one of the best tutorial i have seen and learn something new thanks for sharing your knowledge i have also implemented your this idea in my work and its really worked and helped me
Wow thanks so much. I just uploaded a new one 5 minutes ago. Have a look and let me know: th-cam.com/video/xOWJIlG8XEE/w-d-xo.html
Your great sir...
Thankyou to much for share your excellent experience with us.
Thank you again sir.
For sure, you are very welcome, thanks for your comment Mohd
someone probably pointed this out already but Dim UserRow, SheetCol as Long results in UserRow being a variant data type. you have to explicitly assign a type to each variable. Dim UserRow as Long, SheetCol as Long
Hi Jonathan, yes and thanks. All of my recent videos for the last year have them Dim'd specifically for each one instead of a general. (See some of my more recent videos to see the updates)
Thanks so much for the feedback.
Thank you very much for this. I was positively surprised that it actually works!
For sure, you are very welcome, I am happy to help
Thank you for this tutorial ✨ Really helpful 🥺
For sure, you are very welcome Mai. Happy to help.
Finally at the end ! But I have a new question. How about it MACRO over Users and LoginForm? Your comments at the end the code on load WorkBook Open. Any continuos video at this point?
The action button not make a capture data from LoginForm to puttin in the respective cells. Then usign de rest of macro code to apply.
Hi and thanks so much. I am glad you enjoyed the video. I show you how to do that in this video: th-cam.com/video/o1KdIaug6ac/w-d-xo.html
I hope this helps and thanks so much.
@@ExcelForFreelancers Tks so much Mr. Randy and Congrats for your win prize recently! Bravooo! 🥇
Thanks so much. Still in the nomination phase. I will know in about 2 weeks. Thanks again :)
WOW!! This is what I'm looking for😍
Perfect, I am very glad I could help
Hello, thanks for work.. it's amazing
Correct me if I am wrong, it is supposed that B5 receive the user name from the login panel and same for B6.. but it seems in your work that the login panel is receiving the content of B6 and B5
Hi and thanks for your comment. This training was a few years ago so I am not sure on the cell assignments. Can you share your code in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 50,000 Members who would love to help you with this. Thanks so much.
VERY USEFUL TUTORIAL...
Glad you think so and thanks so much Murali, Thank you for your Likes, Shares & Comments. It really helps.
You are brilliant sir
Thank you very much. I do appreciate that.
very nice tuto (as usual) i love the way you used Windingsfont, makes life so easy :)
The tip about the VBA password came to late for one of my projects, but then again, learning form own mistakes is a great teacher too :D
Thank you so much P. I am really glad you liked it and look forward to creating many more for you.
I agree, never thought of using those. makes the work easier than using controls
Great coding tutorial. I enjoy watching. As programmer point of view, I would be much better if this code follow coding standard and structure. Thanks for sharing.
Thanks so much Steve. I really do appreciate that.
Very detailed explanation. Like it.
Thanks very much. I am really glad you liked it Amit
Great job Sir, Such a deep and details tutorial, very much helpful in dead.
For sure, you are very welcome I am glad you liked it.
Thanks a lot for this video!!!
For sure, you are very welcome I am happy to help
this is awesome! thank you so much for this tutorial!
Thank you so very much, I really appreciate that Damian
Thanks a lot for the tutorial. It helped me a lot.
For sure, you are very welcome Iftakhar, I am glad I could help
Perfect , excellent video
Thank you very much Thomas.
Thanks for this video. It is just want I needed!! :)
Glad it was helpful Cecile, thanks so much.
Thank you for this, wondering how to solve for document being located on SharePoint / Teams site and multiple individuals signing in at the same time? It appears to overwrites the other credentials.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/G9VqF7yN0Oo/w-d-xo.html
I hope this helps and thanks so much.
Great tutorial, thank you for sharing, Cheers Janez
Thanks so much. I really appreciate that.
Great video. Will this work if the file is located in Sharepoint or Onedrive? Looking forward to reading your reply.
Hi and thanks very much. It should work in OneDrive, but not sure about Sharepoint. Please test this with your own settings to see which one is fastest and works best
Very simple formulas and simple code but very good usage
Thanks very much. I appreciate that.
Thank you Dear Respected Sir. Very wonderful. Very professional.
For sure, you are very welcome and I am happy to help and share
This is a great video. Could you tell me how to do the double click and select the entire column instead of the row so that all users could see a certain page, much like what you have in your first column, just working the other way? For example, if you double click G5 it will change from G5 to G26, or H5 will change H5 to H26, so on and so forth.
Hi and thanks you can use the Before Double Click event on any worksheet, then, within that event you can use something like
Range(Cells(5,Target.Column),Cells(26,Target.Column)).Select
Something like that will work.
I hope this helps and thanks so much.
excellent design & coding.
Thank you very much. I do appreciate that Khoi. Cam on em :)
This video deserve more views and likes. Appreciate your share bruh.
Appreciate that and thanks so much.
Great tutorial, thank you! How do I share a macro workbook in OneDrive that won't disable the macro?
HI and thanks so much Pete. I created a specific training on how to share and sync your macro-enabled workbook here: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
I hope this helps and thanks so much.
Hi Randy, thank you so much for your wonderful presentation. This is what I was looking for all my entire life. you are such a good teacher. I have been using excel but didn't know that Visual basic existed. Therefore I decided to follow your tutorial step by step, but my challenge is that I have done all the coding the way you did and successfully run most codes in the security work book but when I run it, I get a message box saying "Run time error '424'
Object required", and when I click on "debug", it highlights a line in the code that says " If.cell(userrow.sheetcol.value = "D" Then". What can I do? I am on the last stages of completing the admin sheet
Hi and thanks so much. I am glad you enjoyed the training. When you have that type of bug, that means your language pack does not recognize that character, so instead of that character with the quotes, you can use the character equivalent such as
chr(208)
instead of "D"
You can find these numbers when you click Insert > Symbols, then use the Webdings Font and look for the character # in the lower right
I hope this helps and thanks so much.
Hi Randy, Thank you very much for this great video. I have a question. How to make the form appear when user opens the workbook through a web link (through sharePoint). Thanks
Hi and thanks so much. Inside the VBA Editor, check the Workbook module which has a Workbook_Open event, you can display a userform on that workbook open event.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Dear Randy
Thank you so so much for great tutorial! Is is possible if when open file, I want to show not only sheet "Start" but also some another sheet?
Hi and thanks for your comment. You can show any sheet on start when you either activate that sheet on Workbook Close event or when you activate the sheet on the Workbook Open event.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so much for the amazing video! Im facing some issues while the code for unprotect “shows script out of range”
Any help would be highly apprised.
Hi Udit, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
You are a remember image of excel, Thank you very much
You are welcome. I am happy to help. Kap Khun Ma Krub.
Hi Randy,
Thanks for sharing much knowledgeable videos i really appreciate your hard work.
I want to know does this file work in Microsoft 365 co-authoring, like on onedrive sharing any of my user can login with there id and it will store the data in data but each user ha different sheet in front of him and rest is very hidden sheet.
Hi and thanks so much. I have just uploaded a training on this. You can share and sync your macro enabled workbook with any desktop version of Excel using a shared folder such as Dropbox, OneDrive or Google Drive. I have a few videos on this, the latest is here
To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html
Thank you so much.
For sure, you are very welcome
Thank you for making your knowledge available
I would like to ask a question.
in this workbook if you have a user I have to hide a column.
how to proceed.
Hi thanks you can hide any column based on a specific user, perhaps something like this.
If username = "testuser" then sheet1.Range("A1").entirecolumn.hidden = True.
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
This tutorial is good. Nevertheless two things have been left out. First, Admin to monitor logins and second restrict users based on number of times they have to login (with Date and time). If there could be an update to this video, it will be the best on youtube ratings on Excel VBA..
Hi thanks very much for your comments. There are probably 100 features i wanted to add, or could have added, however I had to limit the training a bit.
Concerning the Login Dates & Times, I have this in both Change Long 1 & 2 videos here:
Change Log 1: th-cam.com/video/6TnxPvLz_44/w-d-xo.html
Change Log 2: th-cam.com/video/fE15W72vnEE/w-d-xo.html
YOU ARE AMAZING ❤️
Thank you so very much, I really appreciate that Siju.
HEY this was a perfect video i followed it and it worked great now i want to share the link of this workbook but the vba restrictions doesnt seem to work on there side how do i address this please?
HI and thanks so much. Anytime you download a workbook from the internet, the end user must first right click the file, go to properties and click the 'Unblock' macros checkbox before opening it.
As an extra security measure, Microsoft disables macros from workbooks downloaded over the internet. You can fix this once and for all by following the steps I have outlined on this page www.excelforfreelancers.com/how-to-fix-macro-blocking-issue/ I hope this helps and thanks so much.
I am very pleased and very helpful to me, thank you!
For sure you are very welcome Danail. I am glad I can help
Excellent . Does it allows multiple users to work at the same time?
Yes for sure it can. I have a very specific video on that here, that allows you to share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html
I hope this helps and thanks so much.
love your videos! i think if all pages are unlocked and you edit a specific sheet may it be locked or viewable only, the all pages access icon should be removed.
Hi thanks so much Joel, that is a good suggestion and I do appreciate your feedback. Thanks again.
Dear Mr. Austin,
I have just follow your Tutorial about "User Security Rights" . Thank you verry verry much. The Tutorial was verry clear and professional. I have learned a great deal in the past period just by following you and and your lessons. I was able to raise my excel workinglevel to a higher stage.
I am currently working on an Excel programm where I am dealing with different users with different loginlevels. How can I get a copy of your Excel script. I was able to follow you until you reached the Visual Basic part.
Can you send a copy of this excelfile? . Thanks in advance 1000Kx
Cancy
HI Cancy, thanks so much for your comment and I am really glad you enjoyed the video. Every video I do on this channel has a Free Workbook. You would just have to click the link sin the video description to get the workbook. Thanks again.
Hey, awesome tutorial! I have done everything and it's working great. HOWEVER, is there a way to set the active worksheet? so after login, users will move away from the "Start" page automatically to a different worksheet of my choosing??
O wait I just figured it out by adding "Worksheets("Sheetname").Activate" after the "B5,B6" clearance bit which I moved after the sheet protections bit :D everything working like a charm! thanks
Yes great idea. In the Admin screen you can add a drop down list of sheet names in which the user can select as their personal 'start page' you can then set this sheet to activate in VBA on startup.
I hope this helps and thanks so much.
Hi Randy, setting up a Performance dashboard for a number of social care teams. That was the easy bit, however, Area managers do not teams to be able to look at each other's stats so when I came across this I thought WOW. And I was correct does the job perfectly, however, downloaded the workbook which allows me to add additional users but. Cannot add columns to the Admin sheet. I have tried everything, from adding to the conditional formatting, extending the column & row ranges in the vba but does not recognise they are there? It will do the fill all facility but the 3 stages of vision even though applied do not work , can you advise? Otherwise it is a great piece of work that is ideal for my needs
HI thanks Neil, You can remove the formula that displays the sheet names, and just add your own sheet names to the sheet name row. Also update the code accordingly to accommodate your sheet. If you need help with this please feel free to create a post in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel If it is a lot of work, as opposed to a simple question, please mark this post with PAID JOB. I hope this helps and thanks again.
Good explanation 👍👍, Good visual presentation 👍👍 and im very thankful for this tutorial i mange to put this on my project. I hope more tutorial will post that may help others ...
Ps.. is there's a way to hide like this *** on the password when login..?
Hoping for your response ☺️☺️
And thank you in advance ☺️☺️☺️❤️❤️
HI and thanks yes you can find that information here: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
I hope this helps and thanks so much.
@@ExcelForFreelancers thank you sir.. its really helped.. 🙏🙏☺️
Hi, thank you very much for the tutorial! I have a problem with file downloaded, when I login (for example Admin - admin) I have VBA error runtin 9 with debug on Sheets(SheetNm).Unprotect "123", how can to fix it? thank you very much
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hey Randy I need your help! First of all thanks for the great video, I replicated the macro successfully but now I need help knowing how can i have multiple users edit and work on the macro at the same time. I'm using it so they can upload their weekly performance and restricting their access to other tabs to avoid data loss/modifications, i uploaded the macro in onedrive and they open the macro through their desktop app, but when someone closes or saves the macro it will close it for other users, and I don't know how to prevent it from doing that.
Thanks for your time and hopefully your read my comment.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
The idea is that each user has their own Excel application (not the same one). Only the data is shared about users via a shared folder such as OneDrive or Dropbox
I hope this helps and thanks so much.
@@ExcelForFreelancers Thanks Randy! I had originally created the user login from this video, should I change it to the one in the new video? or is there an specific point from the other training i can pick up from to merge both contents or avoid modifying too much?
Hi Thank you for nice tutorial. I was able to implement, however while using with multiple users, workbook closes even when single user close out his / her worksheet
Please advise how to get rid of the above issue
Hi and thanks for your message. You can shar and sync your macro-enabled workbook using this training here: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
I hope this helps and thanks so much.
Thank you very much for the valuable information about Excel VBA :)
For sure, you are very welcome, I am happy to create and share these.
@@ExcelForFreelancers Yes Video was shared via Facebook, Twitter and TH-cam :)
Thank so much. I do appreciate that.
@@ExcelForFreelancers I have a question in worksheet (1) in the data and I want to delete the repeated data in the rest of the other work papers by vba code How do I do this?
Hi thanks you can use a few different filtering techniques including advanced filter. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel
this is very nice ...keep it up we need this..
Thank you very much. I am quite happy to help.
Thanks Randy for the great video. If you don't mind me asking, following this trick it would add different users to the same excel with the same data? What if the excel is being sold to different users/companies Is it still possible to protect it from being re-sold by the users?
Hi and thanks, yes certainly you can protect it from being resold. I have a separate video on that here: th-cam.com/video/l-tgyQvdC5g/w-d-xo.html
I hope this helps and thanks so much.
Thank you so much Randy for this tutorial.
Please can you make security features for a file to become undeletable i.e user unable to delete or kill excel file or any other file?
Thank you once more as we keep learning from you.
Hi Joe, thanks very much for your comment and glad you like the videos. your idea of making a file unable to be deleted would have to do more with computer and user rights then Excel, since only file structure rights of a computer can protect files. Using a backup folder such as DropBox ensures that there is always a backup copy of an application should the file be deleted. Thanks so much.
Thanks a lot buddy! it helped :)
For sure, you are very welcome Ketan
First, amazing videos. Thanks for sharing your knowledge and experience with the rest of us. My question is, after setting up the various rights to view/modify the spreadsheet, can it be used as a shared spreadsheet - several simultaneous users - or can it only be used by one person at a time? Thanks again for all of your videos and I look forward to your reply.
Hi and thanks, yes it can. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
I'm not sure if you're still following these comments but I was also wondering about combining this spreadsheet, which grants different types of privileges to a workbook, and the other video where you show how to make a workbook accessible to several users simultaneously. It seems like they would be a natural fit but didn't know if you might have a template for that. Also, you used an example of a contact list which has mainly unique entires. How could you change to include repeated entries like a purchase log where the same companies may make numerous requests. Thanks! And again, your videos are awesome.
Hi Frey, thanks so much to share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
Thank you for this video, I find it very useful and hopefully I can apply it in my work. However, after minute 37, I got stuck. I receive an error message: Next without For. I have check several times and my code looks just the same as yours in the video. I don't know what could be wrong. Can you please help?
Hi and thanks you my want to check that any IF Statements within your For/Next loop also has an End If inside that loop
I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Hi sir
Undoubtedly It's amazing one.
Is it possible to use this work sheet by multiple user at a time if we give access to each user only dedicated sheet or by using options like workbook sharing
Hi and thanks for your comment. Yes you can certainly. You can follow the training in this video. th-cam.com/video/aKV5seZmiBs/w-d-xo.html I hope this helps and thanks so much.
@@ExcelForFreelancers Dear Sir,
Thanks for swift response.
Video which you have shared is related to usage of separate workbook by different user & then finally synchronized.
My requirement is usage of same excel workbook by same time by multiusers with their individual logins.
With your access rights worksheet, when I used share workbook option, it is becoming single user.
Please help.
your tutorial is fantastic, I really love the script you show which I would like to apply it in my job. Not sure if you could share the script to me so I can learn and digest it more effectively? Thanks a lot in advance.
Hi Kelvin Thanks for your comment. Every one of my TH-cam training contains a free workbook. You can access this workbook from the links in the description.
Excel For Freelancers thanks , I have obtained it 👍🏻
hi, thank you very much for the tutorial! it is great! One question though, how do you get the sheet names on row 4 ? Thank you very much !
Hi and thanks, i use a formula for that, however you can simply type in the sheet name as that is a lot easier. If you would like to see the formula, feel free to download the workbook for free using the links in the description.
I hope this helps and thanks so much.
Hello. Very detailed tutorial, many thanks!. Just wondered if you are able to do one that uses users usernames on a network, so that there is no need for the workbook to request for logon details, but will just open but display/restrict worksheets according to the rights assigned to that particular username?
Hi Rab, thanks for your comment, yes you can share any data on a network as long as that data is synced to the local workbook. I show you how to do that in this video: th-cam.com/video/aKV5seZmiBs/w-d-xo.html
Excellent presentation. Thank you.
Thank's very much. I appreciate that.
hello, this has been very helpful - is there any updated version. each time I try it I encounter a few errors, not sure it's me or if there are excel updates that dont work with this?
Hi and thanks so much. Instead of using the formulas to get the sheet names, just type in the sheet names and that should help.
I am thinking about making an update to this, as you have a great idea since its been so many years since i did this.
You can post your specific issue in our Excel For Freelancers Facebook Group here: bit.ly/groupexcel
We have 60,000 Members who would love to help you with this. Thanks so much.
Hello Randy! Thank you for your useful tutorial! I have one problem with the login page that everything want well and working perfectly at first. But, when I done the code, I save and close the workbook. After reopen the workbook, the Login form was not coming up? Please help me out for the solutions or any code was missing? Thank you in advance.
Hi and thanks make sure the code is in the workbook_Open event and that macros are enabled. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here: bit.ly/groupexcel
Thank you so much!
Thank you for your useful video, Could you please help why error shown as " Run time error 9 Subscript out of range ( Sheets(SheetNm).Unprotect "123" )
Hi Ahmed, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Very useful
Thank you so very much, I really appreciate that Raju
This is wonderful. I just have a quick questions regarding userform for passwords.
Suppose whole team need to use the same sheet for some purpose and We create this macro so that everyone will not able to see what other data we have on different sheet. How can one person ( admin) controls that? As when the sheet opens anyone can see the admin page and update the restrictions/security as per there need.
Is there any way that if admin uses his password then only he able to see the admin page and for other no admin page is visible until they login with correct password? Not even veryhiddensheet way you told in this video.
Hi and thanks very much, and I am glad you liked the training. The Best way to do that is on close, to show a start screen (for all users) then on startup the pages are displayed/hidden based on the user type entered. To share and sync your macro-enabled Excel workbook, please watch this training: th-cam.com/video/f_1HsR9305I/w-d-xo.html
I hope this helps and thanks so much.
@@ExcelForFreelancers Thanks for replying. I am looking for a vba macro which works like a workflow. Which has the ability to assign task from one person to another in workbook, charts and data representation userforms. Which is accessible by all so that every employee can see what task has been assigned to him/her today. Could you please guide me to the collection of videos I can refer for this?
HI and thanks so much. I think my Kanban board will solve that issue here: th-cam.com/video/3qbU5S8ZaEA/w-d-xo.html
If you combine that with the co-authoring link above you will have what you need.
I hope this helps and thanks so much.
I really enjoyed the video and was able to follow along except for the the sheet names and it's formula could give a little more detail on that?
Hi thanks so much for the feedback. Make sure you also download the workbook from the links above. The formula to get the sheet names:
=IFERROR(MID(CELL("filename",Sheet3!$A$1),FIND("]",CELL("filename",Sheet3!$A$1))+1,255),"")
The 'filename' for any specific cell pulls the entire filename of the application including the path, workbook name and sheet name such as:
C:\Users\Fred\Documents\User_Login_Security_Rights-2\[User_Login_Security_Rights.xlsm]Sheet3
But we only need the sheet name. So the formula uses "Find" to find the "]" and then returns all of the characters after this character, with a maximum of 255 characters, which is the actual sheet name
I hope this helps.
Great . Is it possible to do the same but only on a range of a worksheet ?
Yes sure you can protect any range of cells you want with the Cell Protection property and make sure the sheet is also protected with a password. I hope this helps and thanks so much.
@@ExcelForFreelancers thx