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 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.
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
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.
I really appreciate the good quality and easy understanding help in your videos; especially I can use this tutorial about user login. It was quite easy to follow, and create a “personalized” file for our use. However, there is one thing that could be very cool to add, if possible. I’m thinking about a counter, that can tell which users have opened the file, and maybe when
Hi its a great point you make. I am thinking very soon to create a 'Change Log' Application which not only how which users have opened but what changes they have made in the workbook along with an 'Undo Changes' option which would allow a user to reverse the actions made. I think this would be helpful
Great tutorial, and fantastic ideas. I do have one very small suggestion for using the "BeforeDoubleClick" trigger. I like to add "Target.Offset(1).Select", or "Target.Offset(0, 1).Select" in the Sub, that way the cell you double-click does not become active, with the cursor flashing at the end of the character string. I personally find that annoying, so adding that simple line can help a lot. Thanks for the video.
HI and thanks yes that is a great suggestion. In my recent videos I have also added the selection of another cell when I use checkboxes or some single cell selection function. Thank you for your Likes, Shares & Comments. It really helps.
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!
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 RUclips 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.
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
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
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
@@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.
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: ruclips.net/video/f_1HsR9305I/видео.html
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: ruclips.net/video/o1KdIaug6ac/видео.html I hope this helps and thanks so much.
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: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
It's really useful and straightforward. I'm looking for a way to do this, but I would like to have the user list on a different worksheet inside a different folder than the folder I share with my clients. Is there a way to customize the workflow you teach in this video to do what I described? Thank you.
Hi and thanks very much. I just recently created this same concept, but in an add-in, so that it can be used with any workbook. You can watch the full training, and download the free template here: ruclips.net/video/-22SN14gbf0/видео.html I hope this helps and thanks so much.
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: Iruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Thank you for this tutorial. This is an awesome tool. Do you perhaps know how this User security would work with a online shared file and multiple users logging into the same file at the same time?
Hi and thanks very much. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/G9VqF7yN0Oo/видео.html Also have an updated user security option here: ruclips.net/video/-22SN14gbf0/видео.html I hope this helps and thanks so much.
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.
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: ruclips.net/video/f_1HsR9305I/видео.html I hope this helps and thanks so much.
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" RUclips 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.
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
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 and thanks so much Pete. I created a specific training on how to share and sync your macro-enabled workbook here: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and thanks so much.
Thank you for this, wondering how to solve for document being located on SharePoint / Teams site and multiple individuals signing in at the same time? It appears to overwrites the other credentials.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/G9VqF7yN0Oo/видео.html I hope this helps and thanks so much.
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.
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
Wooow fantastic. Can you help me because I don’t works. At 38:39 Min when I protect the sheets and click to to disappear the sheets it doesn’t work. What can the problem be?
HI and thanks can you share a screenshot of your code and application along with the issue in 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.
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 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.
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.
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
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
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.
Hey EFFs, when I tested the code at 35:44 the line "If .Cells(UserRow, SheetCol).Value = "Ð" Then" is bringing up a mismatched error Run-time error '13. Any ideas?
Hi Josh. 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 50,000 Members who would love to help you with this. Thanks so much.
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
Thank you so much for the amazing video! Im facing some issues while the code for unprotect “shows script out of range” Any help would be highly apprised.
Hi Udit, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
Hi Randy, 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
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 your useful video, Could you please help why error shown as " Run time error 9 Subscript out of range ( Sheets(SheetNm).Unprotect "123" )
Hi Ahmed, Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 40,000 Members who would love to help you with this. Thanks so much.
very nice tuto (as usual) i love the way you used Windingsfont, makes life so easy :) The tip about the VBA password came to late for one of my projects, but then again, learning form own mistakes is a great teacher too :D
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: ruclips.net/video/aKV5seZmiBs/видео.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?
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
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. ruclips.net/video/aKV5seZmiBs/видео.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 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, 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: ruclips.net/video/f_1HsR9305I/видео.html
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.
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: ruclips.net/video/aKV5seZmiBs/видео.html I hope this helps and 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.
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: ruclips.net/video/f_1HsR9305I/видео.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: ruclips.net/video/3qbU5S8ZaEA/видео.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 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.
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.
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
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.
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
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 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.
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: ruclips.net/video/aKV5seZmiBs/видео.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.
Hi thanks so much for the feedback. Make sure you also download the workbook from the links above. The formula to get the sheet names: =IFERROR(MID(CELL("filename",Sheet3!$A$1),FIND("]",CELL("filename",Sheet3!$A$1))+1,255),"") The 'filename' for any specific cell pulls the entire filename of the application including the path, workbook name and sheet name such as: C:\Users\Fred\Documents\User_Login_Security_Rights-2\[User_Login_Security_Rights.xlsm]Sheet3 But we only need the sheet name. So the formula uses "Find" to find the "]" and then returns all of the characters after this character, with a maximum of 255 characters, which is the actual sheet name I hope this helps.
Yes for sure, you can add specific protection based on a User Type, so that some cells are protected for certain users and all cells are protected for other users, based on their user type. I hope this helps and thanks so much.
@@ExcelForFreelancers Thank you for your reply - apologies in the delay to respond. Please advise how I could do this. I have used Format Cells to unlock specific cells. Option 1 which protects locked cells works perfectly - these users can enter info into unlocked cells. Option 2 should protect locked AND unlocked cells so these users cannot enter info. This is the one I can't get right. Thank you
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
First, amazing videos. Thanks for sharing your knowledge and experience with the rest of us. My question is, after setting up the various rights to view/modify the spreadsheet, can it be used as a shared spreadsheet - several simultaneous users - or can it only be used by one person at a time? Thanks again for all of your videos and I look forward to your reply.
I'm not sure if you're still following these comments but I was also wondering about combining this spreadsheet, which grants different types of privileges to a workbook, and the other video where you show how to make a workbook accessible to several users simultaneously. It seems like they would be a natural fit but didn't know if you might have a template for that. Also, you used an example of a contact list which has mainly unique entires. How could you change to include repeated entries like a purchase log where the same companies may make numerous requests. Thanks! And again, your videos are awesome.
Hi and thanks so much for your comment. The answer is yes. Your macro-enabled workbook can be shared and synced with users around the world when you follow the training I have created here: ruclips.net/video/aKV5seZmiBs/видео.html
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 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.
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
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.
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
🎄 𝗦𝗮𝘃𝗶𝗻𝗴𝘀 𝗔𝘀 𝗕𝗿𝗶𝗴𝗵𝘁 𝗔𝘀 𝗖𝗵𝗿𝗶𝘀𝘁𝗺𝗮𝘀 𝗟𝗶𝗴𝗵𝘁𝘀 - 𝗨𝗽 𝗧𝗼 𝟱𝟱% 𝗪𝗮𝗶𝘁𝗶𝗻𝗴 𝗙𝗼𝗿 𝗬𝗼𝘂! 👉 www.excelforfreelancers.com/XmasSale2024_YTPinnedComm
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.
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.
Another great video. I thought I was good at coding until now that through your videos I discover that there is so much you can do and at the same time simplify the codes. I still have a long way to go. btw I like your style of teaching, you are not too fast, the pace is perfect. Thank you for your dedication
Thanks so much, I am really glad to share some new ideas and new ways of looking at Excel. Thank you.
Someone from India tried to recreate this without credit.
Follows will follow, leader will lead... You're very right sir.
Yes i saw that and RUclips usually removes them. Thank you for your comment and I appreciate that.
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 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.
⚡ 𝗦𝗜𝗠𝗣𝗟𝗜𝗙𝗬 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗘𝗫𝗖𝗘𝗟 𝗧𝗔𝗦𝗞𝗦 𝗜𝗡𝗦𝗧𝗔𝗡𝗧𝗟𝗬 𝗪𝗜𝗧𝗛 𝗘𝗫𝗖𝗘𝗟 𝗔𝗜 𝗧𝗢𝗢𝗟𝗣𝗔𝗖𝗞 𝗔𝗗𝗗-𝗜𝗡 👉 www.excelforfreelancers.com/ai-toolpack
I really appreciate the good quality and easy understanding help in your videos; especially I can use this tutorial about user login.
It was quite easy to follow, and create a “personalized” file for our use.
However, there is one thing that could be very cool to add, if possible.
I’m thinking about a counter, that can tell which users have opened the file, and maybe when
Hi its a great point you make. I am thinking very soon to create a 'Change Log' Application which not only how which users have opened but what changes they have made in the workbook along with an 'Undo Changes' option which would allow a user to reverse the actions made. I think this would be helpful
@@ExcelForFreelancers That would be very much appreciate! Thank you
Hi did you implement the change log? Thanks so much for sharing your great videos.
Great tutorial, and fantastic ideas. I do have one very small suggestion for using the "BeforeDoubleClick" trigger. I like to add "Target.Offset(1).Select", or "Target.Offset(0, 1).Select" in the Sub, that way the cell you double-click does not become active, with the cursor flashing at the end of the character string. I personally find that annoying, so adding that simple line can help a lot. Thanks for the video.
HI and thanks yes that is a great suggestion. In my recent videos I have also added the selection of another cell when I use checkboxes or some single cell selection function.
Thank you for your Likes, Shares & Comments. It really helps.
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.
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 RUclips 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.
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
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
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.
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.
The "veryHidden" option was very hidden actually. Never knew it existed at all. Thanks Randy :)
For sure, you are very welcome Ganesh, I am happy I could help
You sir, are a genus. Born to instruct. Respect.!!
Thank you so very much, I really appreciate that
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.
all thumbs up from the start to the GREATEStT VBA teacher Randy
Thank you so very much, I really appreciate that
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.
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: ruclips.net/video/f_1HsR9305I/видео.html
The BEST Excel VBA tutorial in RUclipsr ever! Thanks a lot!
Thank you so much. I really appreciate that Binye. I look forward to creating a lot more videos and training.
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: ruclips.net/video/o1KdIaug6ac/видео.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 :)
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: ruclips.net/video/aKV5seZmiBs/видео.html
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.
It's really useful and straightforward. I'm looking for a way to do this, but I would like to have the user list on a different worksheet inside a different folder than the folder I share with my clients. Is there a way to customize the workflow you teach in this video to do what I described? Thank you.
Hi and thanks very much. I just recently created this same concept, but in an add-in, so that it can be used with any workbook.
You can watch the full training, and download the free template here: ruclips.net/video/-22SN14gbf0/видео.html
I hope this helps and thanks so much.
@@ExcelForFreelancers Thank you!
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: Iruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Thank you for this tutorial. This is an awesome tool. Do you perhaps know how this User security would work with a online shared file and multiple users logging into the same file at the same time?
Hi and thanks very much. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/G9VqF7yN0Oo/видео.html
Also have an updated user security option here: ruclips.net/video/-22SN14gbf0/видео.html
I hope this helps and thanks so much.
@@ExcelForFreelancers Thank you for the quick response. These are some awesome tool.
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.
Allow me to thank you wholeheartedly for this very special and wonderful lesson.
For sure, you are very welcome and thanks.
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: ruclips.net/video/f_1HsR9305I/видео.html
I hope this helps and 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.
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" RUclips 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.
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
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.
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: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and thanks so much.
Thank you for this, wondering how to solve for document being located on SharePoint / Teams site and multiple individuals signing in at the same time? It appears to overwrites the other credentials.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/G9VqF7yN0Oo/видео.html
I hope this helps and thanks so much.
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
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
VERY USEFUL TUTORIAL...
Glad you think so and thanks so much Murali, Thank you for your Likes, Shares & Comments. It really helps.
Wooow fantastic. Can you help me because I don’t works. At 38:39 Min when I protect the sheets and click to to disappear the sheets it doesn’t work.
What can the problem be?
HI and thanks can you share a screenshot of your code and application along with the issue in 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.
@@ExcelForFreelancers thanks for your answer. Can you tell me how many sheets can I manage ?
Hi an thanks, there is no limit to the number of sheets you can manage.
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
Very detailed explanation. Like it.
Thanks very much. I am really glad you liked it Amit
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.
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.
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
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: ruclips.net/video/xOWJIlG8XEE/видео.html
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.
You are brilliant sir
Thank you very much. I do appreciate that.
Hey EFFs,
when I tested the code at 35:44 the line "If .Cells(UserRow, SheetCol).Value = "Ð" Then" is bringing up a mismatched error Run-time error '13. Any ideas?
Hi Josh. 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 50,000 Members who would love to help you with this. Thanks so much.
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
Thank you for this tutorial ✨ Really helpful 🥺
For sure, you are very welcome Mai. Happy to help.
Thanks a lot for this video!!!
For sure, you are very welcome I am happy to help
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.
Perfect , excellent video
Thank you very much Thomas.
Very simple formulas and simple code but very good usage
Thanks very much. I appreciate that.
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
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.
WOW!! This is what I'm looking for😍
Perfect, I am very glad I could help
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.
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
Thanks a lot for the tutorial. It helped me a lot.
For sure, you are very welcome Iftakhar, I am glad I could help
very nice tuto (as usual) i love the way you used Windingsfont, makes life so easy :)
The tip about the VBA password came to late for one of my projects, but then again, learning form own mistakes is a great teacher too :D
Thank you so much P. I am really glad you liked it and look forward to creating many more for you.
I agree, never thought of using those. makes the work easier than using controls
This video deserve more views and likes. Appreciate your share bruh.
Appreciate that and thanks so much.
Hey Randy I need your help! First of all thanks for the great video, I replicated the macro successfully but now I need help knowing how can i have multiple users edit and work on the macro at the same time. I'm using it so they can upload their weekly performance and restricting their access to other tabs to avoid data loss/modifications, i uploaded the macro in onedrive and they open the macro through their desktop app, but when someone closes or saves the macro it will close it for other users, and I don't know how to prevent it from doing that.
Thanks for your time and hopefully your read my comment.
Hi and thanks so much. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.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?
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
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. ruclips.net/video/aKV5seZmiBs/видео.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, 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.
YOU ARE AMAZING ❤️
Thank you so very much, I really appreciate that Siju.
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: ruclips.net/video/f_1HsR9305I/видео.html
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.
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: ruclips.net/video/aKV5seZmiBs/видео.html
I hope this helps and 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.
this is awesome! thank you so much for this tutorial!
Thank you so very much, I really appreciate that Damian
excellent design & coding.
Thank you very much. I do appreciate that Khoi. Cam on em :)
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: ruclips.net/video/f_1HsR9305I/видео.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: ruclips.net/video/3qbU5S8ZaEA/видео.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.
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.
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.
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.
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
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.
@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 Dear Respected Sir. Very wonderful. Very professional.
For sure, you are very welcome and I am happy to help and share
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.
What software are you using to record your video and be able to zoom in an out to the specific screen areas?
HI Jas, I am using Camtasia Studio. Thank you very much.
Thank you very much for this. I was positively surprised that it actually works!
For sure, you are very welcome, I am happy to help
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.
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: ruclips.net/video/aKV5seZmiBs/видео.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.
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.
Thanks for this video. It is just want I needed!! :)
Glad it was helpful Cecile, thanks so much.
Hi, is it possible to have users who can enter data into unlocked cells on a protected sheet AND users who can only view the sheets
Yes for sure, you can add specific protection based on a User Type, so that some cells are protected for certain users and all cells are protected for other users, based on their user type. I hope this helps and thanks so much.
@@ExcelForFreelancers Thank you for your reply - apologies in the delay to respond. Please advise how I could do this. I have used Format Cells to unlock specific cells. Option 1 which protects locked cells works perfectly - these users can enter info into unlocked cells. Option 2 should protect locked AND unlocked cells so these users cannot enter info. This is the one I can't get right. Thank you
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
First, amazing videos. Thanks for sharing your knowledge and experience with the rest of us. My question is, after setting up the various rights to view/modify the spreadsheet, can it be used as a shared spreadsheet - several simultaneous users - or can it only be used by one person at a time? Thanks again for all of your videos and I look forward to your reply.
Hi and thanks, yes it can. To share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
I'm not sure if you're still following these comments but I was also wondering about combining this spreadsheet, which grants different types of privileges to a workbook, and the other video where you show how to make a workbook accessible to several users simultaneously. It seems like they would be a natural fit but didn't know if you might have a template for that. Also, you used an example of a contact list which has mainly unique entires. How could you change to include repeated entries like a purchase log where the same companies may make numerous requests. Thanks! And again, your videos are awesome.
Hi Frey, thanks so much to share and sync your macro-enabled Excel workbook, please watch this training: ruclips.net/video/aKV5seZmiBs/видео.html
Can multiple users work on different sheets "at the same time" and it will allow saving?
Hi and thanks so much for your comment. The answer is yes. Your macro-enabled workbook can be shared and synced with users around the world when you follow the training I have created here: ruclips.net/video/aKV5seZmiBs/видео.html
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 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.
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!
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.