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!
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.
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.
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!
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.
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
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 UA-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
@@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.
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
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html I hope this helps and thanks so much.
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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.html
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: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html I hope this helps and thanks so much.
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html I hope this helps and thanks so much.
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: ua-cam.com/video/o1KdIaug6ac/v-deo.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
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.
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 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
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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.html
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html I hope this helps and thanks so 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.
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.
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" UA-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.
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: Iua-cam.com/video/aKV5seZmiBs/v-deo.html I hope this helps and thanks so much.
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.
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
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
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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.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: ua-cam.com/video/3qbU5S8ZaEA/v-deo.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 and thanks so much. You lock any cell when the sheet is protected and VBA can also control this so this way, based on the logged in user, VBA can lock/unlock specific cells based on those user rights. I hope this helps and thanks so much.
I would make sure the sheet in which the pivot tables exist is hidden but unprotected. 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
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html
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.
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.
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.
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. ua-cam.com/video/aKV5seZmiBs/v-deo.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.
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.
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!
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: ua-cam.com/video/aKV5seZmiBs/v-deo.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?
On the if statements to change the lock/unlocked icon ... why use IF's ... why not Select Case? or else if statements. Did you set the cancel = true as well.? Prevents the user from entering the edit mode for the cell on double click. Great to see I have similar ideas to the professionals.
Case would work well. I use case often when there are many options. Elsif would also work well. There are many options for sure, so you can find the one that works best for you. Thanks for your comment.
Yes certainly Andrews. You can ether use the formula or just enter the Sheet names directly in the header row and update the VBA and formatting. 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 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.
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.
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.
Hi Mohammed, thanks so much. It may be possible using the Oath API provided by Microsoft. You can check their documentation. I hope this helps and thanks so much.
Hi Rizwan yes you can, however I try not to use them too often as I find them limiting. 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 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.
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
Hi Randy ... awesome tutorial ... the link to get the workbook is not hyperlinked, please send me the workbook ... thank you again for your valuable lessons
Hi thanks so much for the comment and sorry about the broken link I just fixed it, here it is: Download Your FREE Workbooks From either of the following links: Using Your Email Here: bit.ly/UserLogin_WkBk With Facebook Messenger Here: bit.ly/UserLogin_FbDl
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.
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: ua-cam.com/video/l-tgyQvdC5g/v-deo.html I hope this helps and thanks so much.
Thank you sir for the video. By the way i got one problem here. When i try to log in back there is a pop up message stated “Next without For” under the If .cells(UserRow, SheetCol).Value = “x” Then Sheets(SheetNm).Visible = xlVeryHidden
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, is it possible to create a login registry for the admin that register each logins with date and a timestamp? I'd expect this kind of workbook to be a separated workbook, since the login info may in time become a very long sheet of information & the need to register more than one workbook at the same time. What I'm thinking of is to analyze which workbooks are used the most and by what user. This would help prioritize the need of updating different workbooks accordingly. With the information provided of this registry, one would probably like to make a "Start"-sheet that includes pivot tables based on daily, monthly, yearly and all time. Or/and having username based rows with columns for each workbook created, with a total logins at the bottom. My company uses OneDrive to share different Excel-files amongst the employees. If you've already created a youtube-clip based on this idea, please link it under my comment. I've already bought your "200 Excel Workbook Library" and it's amazing to scroll through! I really enjoy your way of teaching others amazing Excel-concepts!
Hi Jack, thanks so much for your idea and suggestion. Yes sure this is certainly possible. If you want to have some help with this you can write up a complete scope and email it to me. I can then refer a qualified developer to take care of this for you (Randy@ExcelForFreelancers) I hope this helps and thanks so much.
Hi Randy, great video. I copied the code that was entered in WBSecurity. I am coming across a runtime error 9:subscript out of range. I checked the debugger and it is coming from the last line instead highlighting Sheets(SheetNM).visible = xlVeryHidden How do I go about in fixing this issue?
HI and thanks for your comment. You may have an incorrect sheet name, so youc an check that. Instead of using the formulas to generate sheet names, you can type the actual sheet names, exactly as they are, into the cells 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
Great. I joined the group waiting for approval. In the meantime, do you also have a video that adds a code to hide rows based on user name and in different sheet names within the same code in this video?
Hi and thanks very much. I do not have a video on that but a simple if/then statement should get you started If UserType = "Admin" then Sheet1.Range("10:20").EntireRow.Hidden = False Else Sheet1.Range("10:20").EntireRow.Hidden = False End IF Something like that
Hi Randy, I have been looking online for several months for a tutorial that does this. Thank-you, I have managed to follow your guide and add this to my application which is working perfectly. I have one very quick question though which I was hoping you could help with. What I would like to do after they have logged in is hide the start sheet which is the only one visible when you first open the workbook, and redirect them to a new landing page called "Welcome_Sheet" Prior to using the user form and log in security from your tutorial I was using the following code which just meant no matter what sheet it closed on, it would always open on my welcome sheet. Private Sub Workbook_Open() Worksheets("Welcome_Sheet").Activate End Sub I want it to still only open on the start screen until they have logged in (exactly as per your tutorial) and all of the sheets that they have been granted access to should open once they successfully log in (again, exactly as per your tutorial) but I just want them to be moved to my welcome sheet at that point however, I can't quite work out how to put this back in and hide the Start sheet after log in. Please could you help? Thank-you again.
The best way to achieve this would be to put all the code in the close workbook event (not the open event) Simply 1st activate your Start sheet, then add code that hides every other sheet as long as it is not the start sheet. This way each time the workbook is opened, it opens on the start sheet. After login, activate any other sheet, then hide the start sheet. 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
Excel For Freelancers - Hi Randy, thanks for the quick reply. I’ve requested to join the FB group as suggested & my request is pending. Coding is fairly new to me so unfortunately your reply isn’t as basic as I need it to be to understand. 😂 I have basically used the exact code you have done in your tutorial (just with more sheets), so currently exactly like your tutorial it is set to only show the welcome sheet until the log in user form is correctly filled out. Then it opens all of the sheets as per the security/access settings for that user in the admin sheet. So I don’t want to restrict them to ONLY see the start sheet once they’ve logged in, I still want them to have all of the sheets that they should be allowed to see, I just want it to divert them to the start sheet after log in and hide the welcome sheet, but I’m confused exactly what to type & where to put it.
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.
Thank you. This was an awesome tutorial. I'm just wondering if it's possible to build on this to only show specific user data on any sheet when using slicers as the filter???? Any thoughts or ideas on how this could be achieved?
Hi Bryan, thanks that is a great question. I am sure it is possible using sliders and hidden rows/columns. 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
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 and thanks very much. You can download this file, and check the formula, using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
Good afternoon Randy this is a fantastic video thank you. I have a question though. Is there a way that we can receive the same desired effect using the windows login automatically to log in. The above example works perfectly but my sheet has several users simultaneously working on it.
Hi Jake thanks so much for your comment. Yes you can have multiple users share and sync your macro-enabled workbook. This training will show you how: ua-cam.com/video/aKV5seZmiBs/v-deo.html
This was a great help but I have one question with using the UserTable and the UserName as tables you can not share this workbook it errors out can the table be converted to ranges in the formula that is in B7?
Hi thanks for your question. I have developed my own method for sharing workbooks which will not cause an error. The training and free video can be found here: ua-cam.com/video/WI-uhkrnk2I/v-deo.html Also with the Employee Manager, in the coming weeks we will be adding User Security and Workbook Sharing so you will want to make sure and catch that. Thanks for your comment.
Dear Randy, Thank you again for such a great idea. I have been checking if there was a part 2 for this. You havnt mentioned how the rest of the sheets works. Like how the date and time will be recorded for each person? Is there a code for that or did I just miss that part? Hope you could let me know. Thank you again Regards
Hi Avi, thanks for your message. This was just a single video to show the potential of what you can do with a user security. I will cover this completely in the mentorship program launching this month or also feel free to check out my Employee Manager series which also has a user login and security feature. Thanks again.
Yes for sure. I will make sure to have announcements in my group, on my website, in the UA-cam videos and everywhere. I am trying to complete everything by the end of this month.
Hi, thanks for a really insightful video. Im experiencing an issue where 2 users are logged in to the workbook at the same time. If user 1 has access to all sheets and then user 2 logs in but only has access to specific sheets, the access changes for user 1. Also if user 2 closes the workbook while user 1 still has it open, all worksheets close except the 'login' or 'welcome' sheet. Any ideas how i can get around this?
Hi and thanks very much for your comment. You can share your macro enabled workbook when each user has their own workbook (not the same workbook but a duplicate) and only the data is shared and synced between them. I show you how you can achieve this in this video: ua-cam.com/video/aKV5seZmiBs/v-deo.html I hope this helps and thanks so much.
Thank you very much dear. Great work, thanks.That's what i need. It's working great. But when i put this file to local network , i can't open this file there. I can't see very hidden files. Only Start sheet. There is show macro is disable. How can I solve this problem? Thanks.
Hi and thanks for your comment. You must enable macros in order for this to work. Make sure that you are not trying to use the application within a browser as well. 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 Randy, i wonder can i use IP prorection for user login istead of cteating user name, ( I mean I want to prevent to open file except some computers ) thanks for help :)
Hi and thanks very much. Feel free to download this and customize it however you like. If you need help with this, I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.
⚡ 𝗦𝗜𝗠𝗣𝗟𝗜𝗙𝗬 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗘𝗫𝗖𝗘𝗟 𝗧𝗔𝗦𝗞𝗦 𝗜𝗡𝗦𝗧𝗔𝗡𝗧𝗟𝗬 𝗪𝗜𝗧𝗛 𝗘𝗫𝗖𝗘𝗟 𝗔𝗜 𝗧𝗢𝗢𝗟𝗣𝗔𝗖𝗞 𝗔𝗗𝗗-𝗜𝗡 👉 www.excelforfreelancers.com/ai-toolpack
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.
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.
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.
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 UA-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.
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.
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.
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 UA-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.
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.
You sir, are a genus. Born to instruct. Respect.!!
Thank you so very much, I really appreciate that
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.
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.
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
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html
I hope this helps and thanks so much.
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
all thumbs up from the start to the GREATEStT VBA teacher Randy
Thank you so very much, I really 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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.html
The BEST Excel VBA tutorial in UA-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.
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: ua-cam.com/video/G9VqF7yN0Oo/v-deo.html
I hope this helps and thanks so much.
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.
Allow me to thank you wholeheartedly for this very special and wonderful lesson.
For sure, you are very welcome and thanks.
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.
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.
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html
I hope this helps and thanks so much.
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: ua-cam.com/video/o1KdIaug6ac/v-deo.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 :)
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
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.
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 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
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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.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.
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.
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: ua-cam.com/video/f_1HsR9305I/v-deo.html
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html
I hope this helps and thanks so much.
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.
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.
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" UA-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.
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: Iua-cam.com/video/aKV5seZmiBs/v-deo.html
I hope this helps and thanks so much.
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.
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: ua-cam.com/video/xOWJIlG8XEE/v-deo.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
Very simple formulas and simple code but very good usage
Thanks very much. I appreciate that.
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
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: ua-cam.com/video/f_1HsR9305I/v-deo.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: ua-cam.com/video/3qbU5S8ZaEA/v-deo.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.
would love to know how to also lock certain data within a sheet or filter lock certain data with in a sheet per each user login if possible please
Hi and thanks so much. You lock any cell when the sheet is protected and VBA can also control this so this way, based on the logged in user, VBA can lock/unlock specific cells based on those user rights.
I hope this helps and thanks so much.
so what would the code be for example. sheet that is protected but want to allow them to use a slicer. many thanks
I would make sure the sheet in which the pivot tables exist is hidden but unprotected.
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
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: ua-cam.com/video/aKV5seZmiBs/v-deo.html
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.
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.
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.
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
Very detailed explanation. Like it.
Thanks very much. I am really glad you liked it Amit
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. ua-cam.com/video/aKV5seZmiBs/v-deo.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.
VERY USEFUL TUTORIAL...
Glad you think so and thanks so much Murali, Thank you for your Likes, Shares & Comments. It really helps.
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.
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
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: ua-cam.com/video/aKV5seZmiBs/v-deo.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?
Thanks a lot for the tutorial. It helped me a lot.
For sure, you are very welcome Iftakhar, I am glad I could help
On the if statements to change the lock/unlocked icon ... why use IF's ... why not Select Case?
or else if statements.
Did you set the cancel = true as well.? Prevents the user from entering the edit mode for the cell on double click.
Great to see I have similar ideas to the professionals.
Case would work well. I use case often when there are many options. Elsif would also work well. There are many options for sure, so you can find the one that works best for you. Thanks for your comment.
Is there a way of extending the number of columns Randy in the admin screen?
Yes certainly Andrews. You can ether use the formula or just enter the Sheet names directly in the header row and update the VBA and formatting. 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
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.
Thank you for this tutorial ✨ Really helpful 🥺
For sure, you are very welcome Mai. Happy to help.
Cycling through Blank as well as the Webdings for the cell icons may be a good idea.
Great point, Thanks so much for your input
ua-cam.com/video/QksNwYTlBXY/v-deo.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.
WOW!! This is what I'm looking for😍
Perfect, I am very glad I could help
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.
Thanks a lot for the tutorial.
However, Is it possible to auto-login using Microsoft account credentials?
Hi Mohammed, thanks so much. It may be possible using the Oath API provided by Microsoft. You can check their documentation. I hope this helps and thanks so much.
@Excel For Freelancers can you use userform with specific sheet wise accessibility. Waiting for your fast response.
Hi Rizwan yes you can, however I try not to use them too often as I find them limiting. 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 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.
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!
In addition, you mention the data is in a table and that is exactly what I am using (Excel Table). Please let me know if that is the issue.
HI and thanks, if you post in our group we can take a look at the issue.
You are brilliant sir
Thank you very much. I do appreciate that.
Hi Randy ... awesome tutorial ... the link to get the workbook is not hyperlinked, please send me the workbook ... thank you again for your valuable lessons
Hi thanks so much for the comment and sorry about the broken link I just fixed it, here it is:
Download Your FREE Workbooks From either of the following links:
Using Your Email Here: bit.ly/UserLogin_WkBk
With Facebook Messenger Here: bit.ly/UserLogin_FbDl
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.
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: ua-cam.com/video/l-tgyQvdC5g/v-deo.html
I hope this helps and thanks so much.
Thank you sir for the video. By the way i got one problem here. When i try to log in back there is a pop up message stated “Next without For” under the If .cells(UserRow, SheetCol).Value = “x” Then Sheets(SheetNm).Visible = xlVeryHidden
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.
Hi…thank you for the reply. I have post in fb group for help🤣
Hey Randy, is it possible to create a login registry for the admin that register each logins with date and a timestamp?
I'd expect this kind of workbook to be a separated workbook, since the login info may in time become a very long sheet of information & the need to register more than one workbook at the same time.
What I'm thinking of is to analyze which workbooks are used the most and by what user. This would help prioritize the need of updating different workbooks accordingly.
With the information provided of this registry, one would probably like to make a "Start"-sheet that includes pivot tables based on daily, monthly, yearly and all time. Or/and having username based rows with columns for each workbook created, with a total logins at the bottom.
My company uses OneDrive to share different Excel-files amongst the employees.
If you've already created a youtube-clip based on this idea, please link it under my comment. I've already bought your "200 Excel Workbook Library" and it's amazing to scroll through!
I really enjoy your way of teaching others amazing Excel-concepts!
Hi Jack, thanks so much for your idea and suggestion. Yes sure this is certainly possible. If you want to have some help with this you can write up a complete scope and email it to me. I can then refer a qualified developer to take care of this for you (Randy@ExcelForFreelancers)
I hope this helps and thanks so much.
Great tutorial, thank you for sharing, Cheers Janez
Thanks so much. I really appreciate that.
Hi Randy, great video. I copied the code that was entered in WBSecurity. I am coming across a runtime error 9:subscript out of range. I checked the debugger and it is coming from the last line instead highlighting Sheets(SheetNM).visible = xlVeryHidden How do I go about in fixing this issue?
HI and thanks for your comment. You may have an incorrect sheet name, so youc an check that. Instead of using the formulas to generate sheet names, you can type the actual sheet names, exactly as they are, into the cells
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
Great. I joined the group waiting for approval. In the meantime, do you also have a video that adds a code to hide rows based on user name and in different sheet names within the same code in this video?
Hi and thanks very much. I do not have a video on that but a simple if/then statement should get you started
If UserType = "Admin" then
Sheet1.Range("10:20").EntireRow.Hidden = False
Else
Sheet1.Range("10:20").EntireRow.Hidden = False
End IF
Something like that
this is awesome! thank you so much for this tutorial!
Thank you so very much, I really appreciate that Damian
Hi Randy, I have been looking online for several months for a tutorial that does this. Thank-you, I have managed to follow your guide and add this to my application which is working perfectly.
I have one very quick question though which I was hoping you could help with.
What I would like to do after they have logged in is hide the start sheet which is the only one visible when you first open the workbook, and redirect them to a new landing page called "Welcome_Sheet"
Prior to using the user form and log in security from your tutorial I was using the following code which just meant no matter what sheet it closed on, it would always open on my welcome sheet.
Private Sub Workbook_Open()
Worksheets("Welcome_Sheet").Activate
End Sub
I want it to still only open on the start screen until they have logged in (exactly as per your tutorial) and all of the sheets that they have been granted access to should open once they successfully log in (again, exactly as per your tutorial) but I just want them to be moved to my welcome sheet at that point however, I can't quite work out how to put this back in and hide the Start sheet after log in. Please could you help?
Thank-you again.
The best way to achieve this would be to put all the code in the close workbook event (not the open event)
Simply 1st activate your Start sheet, then add code that hides every other sheet as long as it is not the start sheet.
This way each time the workbook is opened, it opens on the start sheet.
After login, activate any other sheet, then hide the start sheet.
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
Excel For Freelancers - Hi Randy, thanks for the quick reply. I’ve requested to join the FB group as suggested & my request is pending.
Coding is fairly new to me so unfortunately your reply isn’t as basic as I need it to be to understand. 😂
I have basically used the exact code you have done in your tutorial (just with more sheets), so currently exactly like your tutorial it is set to only show the welcome sheet until the log in user form is correctly filled out. Then it opens all of the sheets as per the security/access settings for that user in the admin sheet. So I don’t want to restrict them to ONLY see the start sheet once they’ve logged in, I still want them to have all of the sheets that they should be allowed to see, I just want it to divert them to the start sheet after log in and hide the welcome sheet, but I’m confused exactly what to type & where to put it.
Thanks very much. Hopefully our members were able to resolve this for you.
@@simonbarry40 Hi Simon, were you able to resolve this please? If so, please advise how you did it. Cheers
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.
Thank you. This was an awesome tutorial. I'm just wondering if it's possible to build on this to only show specific user data on any sheet when using slicers as the filter???? Any thoughts or ideas on how this could be achieved?
Hi Bryan, thanks that is a great question. I am sure it is possible using sliders and hidden rows/columns. 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 hey May the Lord bless you guys, angels from the heavens, ive already joined the group! see you!
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.
Thanks a lot for this video!!!
For sure, you are very welcome I am happy to help
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.
Hello Randy. Can direct me to a tutorial that deal with the formula naming the sheet name on row4.
Hi and thanks very much. You can download this file, and check the formula, using the links in the description above with either your email or Facebook Messenger. I hope this helps and thanks.
excellent design & coding.
Thank you very much. I do appreciate that Khoi. Cam on em :)
Good afternoon Randy this is a fantastic video thank you. I have a question though. Is there a way that we can receive the same desired effect using the windows login automatically to log in. The above example works perfectly but my sheet has several users simultaneously working on it.
Hi Jake thanks so much for your comment. Yes you can have multiple users share and sync your macro-enabled workbook. This training will show you how: ua-cam.com/video/aKV5seZmiBs/v-deo.html
This was a great help but I have one question with using the UserTable and the UserName as tables you can not share this workbook it errors out can the table be converted to ranges in the formula that is in B7?
Hi thanks for your question. I have developed my own method for sharing workbooks which will not cause an error. The training and free video can be found here: ua-cam.com/video/WI-uhkrnk2I/v-deo.html
Also with the Employee Manager, in the coming weeks we will be adding User Security and Workbook Sharing so you will want to make sure and catch that. Thanks for your comment.
Dear Randy, Thank you again for such a great idea.
I have been checking if there was a part 2 for this. You havnt mentioned how the rest of the sheets works. Like how the date and time will be recorded for each person?
Is there a code for that or did I just miss that part?
Hope you could let me know.
Thank you again
Regards
Hi Avi, thanks for your message. This was just a single video to show the potential of what you can do with a user security. I will cover this completely in the mentorship program launching this month or also feel free to check out my Employee Manager series which also has a user login and security feature. Thanks again.
Excel For Freelancers oh that's great RANDY.
I will check it out.
When is the mentorship program starting?
Could you send me a link please...
Yes for sure. I will make sure to have announcements in my group, on my website, in the UA-cam videos and everywhere. I am trying to complete everything by the end of this month.
Hi, thanks for a really insightful video. Im experiencing an issue where 2 users are logged in to the workbook at the same time. If user 1 has access to all sheets and then user 2 logs in but only has access to specific sheets, the access changes for user 1. Also if user 2 closes the workbook while user 1 still has it open, all worksheets close except the 'login' or 'welcome' sheet. Any ideas how i can get around this?
Hi and thanks very much for your comment. You can share your macro enabled workbook when each user has their own workbook (not the same workbook but a duplicate) and only the data is shared and synced between them. I show you how you can achieve this in this video: ua-cam.com/video/aKV5seZmiBs/v-deo.html
I hope this helps and thanks so much.
Thank you very much dear. Great work, thanks.That's what i need. It's working great. But when i put this file to local network , i can't open this file there. I can't see very hidden files. Only Start sheet.
There is show macro is disable. How can I solve this problem? Thanks.
Hi and thanks for your comment. You must enable macros in order for this to work. Make sure that you are not trying to use the application within a browser as well. 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 Randy, i wonder can i use IP prorection for user login istead of cteating user name, ( I mean I want to prevent to open file except some computers ) thanks for help :)
Hi and thanks very much. Feel free to download this and customize it however you like. If you need help with this, I would be happy to refer this out to a qualified developer. Can you please email me all of your specifications in a detailed scope and I can forward it to a developer? Randy@ExcelForFreelancers.com Thanks so much.