The AMAZING TRICK To Add Different User Security Rights to ANY Excel Sheet and User Login

🎁 SAVE UP TO 40% IN JULY 2024 DURING OUR BIRTHDAY CELEBRATION ► rebrand.ly/7thAnniv_YTDesc
🔥 GET THIS + 350 OF MY BEST DONE-FOR-YOU TEMPLATES HERE ► rebrand.ly/350Wkbks_YTDesc
📚 GET A DISCOUNTED DEVELOPERS LIBRARY HERE ► rebrand.ly/UDVL_YTDesc
🤖BRING AI INTO EXCEL WITH THIS INCREDIBLE ADD-IN ► rebrand.ly/AIToolpack_YTDesc
👉CREATE & SELL EXCEL BASED SOFTWARE IN MY MENTORSHIP PROGRAM ► rebrand.ly/MentorshipProgram_...
▶️ GET ACCESS TO OVER 100 UPDATED TRAININGS, WORKBOOKS & PDF CODEBOOKS: rebrand.ly/Patreon_YTDesc
👨‍💼BECOME AN EXPERT AT DASHBOARDS ► rebrand.ly/ExcelDashboard_YTDesc
📣START LEARNING VBA HERE ► rebrand.ly/VBAEFFDan_YTDesc
🏝BECOME A SUCCESSFUL FREELANCER ► rebrand.ly/FreelancersAcademy...
⭐️JOIN KZbin MEMBERS FOR UNRELEASED TRAININGS ► rebrand.ly/YTMembership_YTDesc
In this week’s training, I will show you how to create User Login AND apply different security for each sheet.
⬇DOWNLOAD THIS WEEKS WORKBOOK FREE:
📩 USING YOUR EMAIL: bit.ly/UserLogin_WkBk
🤑 START EARNING BIG $$$ WITH MY AFFILIATE PROGRAM 👉 rebrand.ly/Affiliate_YTDesc
EXCEL COURSES:
►FREELANCER ACADEMY: rebrand.ly/FreelancersAcademy...
►MENTORSHIP PROGRAM: rebrand.ly/MentorshipProgram_...
►DASHBOARD MASTERCLASS: rebrand.ly/ExcelDashboard_YTDesc
►BEGINNERS VBA COURSE: rebrand.ly/VBAEFFDan_YTDesc
►WEB AUTOMATION COURSE WITH EXCEL VBA: rebrand.ly/WebAuto_YTDesc
EXCEL PRODUCTS:
►Turbocharge Your App Development: rebrand.ly/UDVL_YTDesc
►Get 350 Templates In 1 Zip File: rebrand.ly/350Wkbks_YTDesc
►Get ChatGPT Into Excel With This Addin: rebrand.ly/AIToolpack_YTDesc
►The Ultimate Excel Resource Guide: rebrand.ly/UERG_YTDesc
►Get Your Actual Employee Hourly Cost: rebrand.ly/LBC_YTDesc
►1000 Incredible Freelancing Resources: rebrand.ly/UFRG_YTDesc
►Secure Your Excel Applications From Theft: rebrand.ly/XLSPadlock_YTDescrptn
🧐Find the Best Excel Products & Courses Here: rebrand.ly/ExcelProductsAndCo...
TIMESTAMPS:
0:00 - Introduction
0:30 - Overview
2:06 - Insert Symbols
8:19 - Before Double Click
17:46 - Check User
39:39 - Insert User Form
44:53 - Close Workbook
JOIN OUR COMMUNITY:
►Facebook Group: rebrand.ly/EFF_Group
►Facebook Fan Page: rebrand.ly/EFF_FanPage
Follow me on my Social Media Accounts:
🤩TikTok: / excel4freelancers
🐦Twitter: / excel4freelance
🌏Website: www.ExcelForFreelancers.com
🔗LinkedIn: / excelforfreelancers
👤Facebook: / excelforfreelancers
📸 Instagram: / excelforfreelancers
🎥Rumble: rumble.com/c/ExcelForFreelancers
✉Telegram: t.me/ExcelForFreelancers
About Us:
I help Microsoft Excel enthusiasts turn their passion into profits so they can earn passive income using their skills without having to trade time for money.
I love making these videos for you each and every week. A great way of supporting Excel For Freelancers to keep this training free each and every week is by supporting us through one of the amazing products offered that will help you skyrocket your Excel Skills and reach your dreams.
Get Alerted IMMEDIATELY with new Free Training Videos & Workbook by subscribing here:
👉 / excelforfreelancers
New Training Video Uploaded every Tuesday!
Thank you very much for your continued support,
Randy Austin
#exceltraining #userlogin #securityrights #usersecurity

[@ExcelForFreelancers]

🎉 7th Anniversary Sale: Save Big This July 2024! ► rebrand.ly/7thAnniv_YTPinnedCom

[@macman007747]

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!

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Rob. Glad you liked the training.

[@harrylarmer4295]

Super cool tutorial, thank you very much. The steps are well explained and the context you provide in your commentary is gold.

[@ExcelForFreelancers]

Thanks so much Toby, I am really glad you enjoyed the trainnig.

[@excel-businessinexcel5181]

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!

[@ExcelForFreelancers]

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.

[@gayboynz1]

OMG I just spent the last three hours going thru this Step by Step totally helping and awesome. Thank you so much.

[@ExcelForFreelancers]

Fantastic. So glad you liked it Adam. Have a look at my other videos and let me know what you think. If you ever need help, please join our Excel For Freelancers Facebook group here: bit.ly/groupexcel

[@Kestra1207]

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!

[@ExcelForFreelancers]

You are very welcome Monica. Thanks so much for your comment. I am glad I can help and share.

[@EOO-Stand]

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

[@ExcelForFreelancers]

Thanks so much, I am really glad to share some new ideas and new ways of looking at Excel. Thank you.

[@landgegaile6957]

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.

[@ExcelForFreelancers]

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

[@ivanbork4175]

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

[@ExcelForFreelancers]

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

[@moesadr3342]

@@ExcelForFreelancers That would be very much appreciate! Thank you

[@Eric-qm4vm]

Hi did you implement the change log? Thanks so much for sharing your great videos.

[@Hildgud]

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?

[@ExcelForFreelancers]

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

[@binyedeng4430]

The BEST Excel VBA tutorial in KZbinr ever! Thanks a lot!

[@ExcelForFreelancers]

Thank you so much. I really appreciate that Binye. I look forward to creating a lot more videos and training.

[@9323793901]

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

[@ExcelForFreelancers]

Wow thanks so much. I just uploaded a new one 5 minutes ago. Have a look and let me know: kzbin.info/www/bejne/roC6e3yifJ2Le6c

[@aprilshowersmayflowers]

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?

[@ExcelForFreelancers]

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

[@aprilshowersmayflowers]

Thank you so much! That worked perfectly! I'm very excited to use this for my team!

[@ExcelForFreelancers]

Great I am happy to help

[@vickyli3187]

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

[@ExcelForFreelancers]

​@@vickyli3187 HI Vicky, please use Chr(208) instead of "Ð" inside the code. I hope this helps and thanks so much.

[@ganeshs1360]

The "veryHidden" option was very hidden actually. Never knew it existed at all. Thanks Randy :)

[@ExcelForFreelancers]

For sure, you are very welcome Ganesh, I am happy I could help

[@jayateerthkatti7517]

Great man. You are an Uppercut in Knowledge and more above you are sharing knowledge to all.. Best Luck for next Tutorial.

[@ExcelForFreelancers]

Thank you very much Jayateerth, I am really glad you liked it.

[@StarChild1602]

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

[@ExcelForFreelancers]

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.

[@mohammadrashedulislam179]

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

[@ExcelForFreelancers]

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.

[@SimonsKitchenUK]

Same here. Did you get a solution? I think I have fixed it!

[@nejatbocnak1699]

@@SimonsKitchenUK hello. ı am having same problem. how did u solve it ? thx.

[@shashankshekhar1178]

You are an awesome guy, I have learnt a lot from you. You resolve every problem of mine related to excel.

[@ExcelForFreelancers]

Thank you so much Shashank, I really do appreciate that.

[@MAhmad-dr6nz]

Great job Sir, Such a deep and details tutorial, very much helpful in dead.

[@ExcelForFreelancers]

For sure, you are very welcome I am glad you liked it.

[@adil196]

Your great sir... Thankyou to much for share your excellent experience with us. Thank you again sir.

[@ExcelForFreelancers]

For sure, you are very welcome, thanks for your comment Mohd

[@janezklun]

Great tutorial, thank you for sharing, Cheers Janez

[@ExcelForFreelancers]

Thanks so much. I really appreciate that.

[@abdullahalklbani7454]

Allow me to thank you wholeheartedly for this very special and wonderful lesson.

[@ExcelForFreelancers]

For sure, you are very welcome and thanks.

[@DVSgolddust]

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

[@ExcelForFreelancers]

Thank you so much P. I am really glad you liked it and look forward to creating many more for you.

[@EOO-Stand]

I agree, never thought of using those. makes the work easier than using controls

[@hirukarucm1133]

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.

[@ExcelForFreelancers]

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.

[@herbertbeukenboom8547]

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

[@ExcelForFreelancers]

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.

[@MICtheG]

Hi Herbert @Herbertbeukenboom8546 Please share your updated template for our benefit. Thank you.

[@danailborisov1969]

I am very pleased and very helpful to me, thank you!

[@ExcelForFreelancers]

For sure you are very welcome Danail. I am glad I can help

[@johanschurink7744]

Thank you for this video, I did learn something ;)

[@ExcelForFreelancers]

That's great. I am happy to hear that.

[@jeffmartinez262]

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.

[@ExcelForFreelancers]

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.

[@esapekkaturunen488]

Thank you very much for this. I was positively surprised that it actually works!

[@ExcelForFreelancers]

For sure, you are very welcome, I am happy to help

[@kenthomson9562]

Excellent presentation. Thank you.

[@ExcelForFreelancers]

Thank's very much. I appreciate that.

[@maisarah6803]

Thank you for this tutorial ✨ Really helpful 🥺

[@ExcelForFreelancers]

For sure, you are very welcome Mai. Happy to help.

[@damiduran]

this is awesome! thank you so much for this tutorial!

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Damian

[@ezkul95828]

Great coding tutorial. I enjoy watching. As programmer point of view, I would be much better if this code follow coding standard and structure. Thanks for sharing.

[@ExcelForFreelancers]

Thanks so much Steve. I really do appreciate that.

[@iftakhar_hasan]

Thanks a lot for the tutorial. It helped me a lot.

[@ExcelForFreelancers]

For sure, you are very welcome Iftakhar, I am glad I could help

[@ritugoel2202]

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

[@ExcelForFreelancers]

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.

[@joecrack5583]

Thank you so much Randy for this tutorial. Please can you make security features for a file to become undeletable i.e user unable to delete or kill excel file or any other file? Thank you once more as we keep learning from you.

[@ExcelForFreelancers]

Hi Joe, thanks very much for your comment and glad you like the videos. your idea of making a file unable to be deleted would have to do more with computer and user rights then Excel, since only file structure rights of a computer can protect files. Using a backup folder such as DropBox ensures that there is always a backup copy of an application should the file be deleted. Thanks so much.

[@edsonmartins9573]

You sir, are a genus. Born to instruct. Respect.!!

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that

[@CesarPeron]

You're really good, thanks for sharing

[@ExcelForFreelancers]

Thanks so much Cesar, much appreciated.

[@dhanunjayab3404]

Very simple formulas and simple code but very good usage

[@ExcelForFreelancers]

Thanks very much. I appreciate that.

[@pjferreira54]

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.

[@ExcelForFreelancers]

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

[@amitshah8508]

Very detailed explanation. Like it.

[@ExcelForFreelancers]

Thanks very much. I am really glad you liked it Amit

[@ahmaddin7565]

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

[@ExcelForFreelancers]

Hi and thanks so much. You can have multiple people work on your macro-enabled workbook using the techniques I teach in this video: kzbin.info/www/bejne/l3y5Zqabj9KceNU I hope this helps and thanks so much.

[@justiceakoi4083]

Thanks for the reply. Any tutorial on School Management system or employee management system

[@ExcelForFreelancers]

Hi thanks for your comments. I currently don't have any solutions for school management however for Employees, my latest video shows a Payroll Manager & HTML Email feature that you might be interested in here: kzbin.info/www/bejne/hZywYmuKeZaZeqs

[@hossammostafa649]

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 ?

[@ExcelForFreelancers]

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: kzbin.info/www/bejne/nJCUeaaIbphja6s

[@adriantoma9796]

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!

[@ExcelForFreelancers]

Hi that's a great idea. good going, and thanks very much for the compliment. Glad you enjoy the videos.

[@thecopycats9395]

Hi how can i add this

[@cecileclarke6585]

Thanks for this video. It is just want I needed!! :)

[@ExcelForFreelancers]

Glad it was helpful Cecile, thanks so much.

[@Jojosmith342]

all thumbs up from the start to the GREATEStT VBA teacher Randy

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that

[@rabnesbit8287]

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?

[@ExcelForFreelancers]

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: kzbin.info/www/bejne/l3y5Zqabj9KceNU

[@SyedMuzammilMahasanShahi]

Thanks for this wonderful video

[@ExcelForFreelancers]

Thanks so much

[@N7eptune]

Cycling through Blank as well as the Webdings for the cell icons may be a good idea.

[@ExcelForFreelancers]

Great point, Thanks so much for your input

[@tarasolutions3128]

kzbin.info/www/bejne/h5zWf6qPidF1jrs

[@tarot..by..kobkeepintouch]

You are a remember image of excel, Thank you very much

[@ExcelForFreelancers]

You are welcome. I am happy to help. Kap Khun Ma Krub.

[@SM-tz1hc]

Thank you Dear Respected Sir. Very wonderful. Very professional.

[@ExcelForFreelancers]

For sure, you are very welcome and I am happy to help and share

[@shadricksiame5741]

this is very nice ...keep it up we need this..

[@ExcelForFreelancers]

Thank you very much. I am quite happy to help.

[@furdeansergiu1007]

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 !

[@ExcelForFreelancers]

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.

[@johnadam7996]

Thank you for the great tutorial. I have one question. How do i get the user form to link in MS Office 2011 for Mac as control source is not available?

[@ExcelForFreelancers]

Hi thanks very much for the comment. I am not an expert on Mac however If you search for 'Ron DeBruin' his website has tons of great info for Excel with Mac. Thank you

[@saidyahya7343]

Someone from India tried to recreate this without credit. Follows will follow, leader will lead... You're very right sir.

[@ExcelForFreelancers]

Yes i saw that and KZbin usually removes them. Thank you for your comment and I appreciate that.

[@brianwollman3462]

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?

[@ExcelForFreelancers]

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.

[@umartwheelz7957]

Hi Randy, thank you so much for your wonderful presentation. This is what I was looking for all my entire life. you are such a good teacher. I have been using excel but didn't know that Visual basic existed. Therefore I decided to follow your tutorial step by step, but my challenge is that I have done all the coding the way you did and successfully run most codes in the security work book but when I run it, I get a message box saying "Run time error '424' Object required", and when I click on "debug", it highlights a line in the code that says " If.cell(userrow.sheetcol.value = "D" Then". What can I do? I am on the last stages of completing the admin sheet

[@ExcelForFreelancers]

Hi and thanks so much. I am glad you enjoyed the training. When you have that type of bug, that means your language pack does not recognize that character, so instead of that character with the quotes, you can use the character equivalent such as chr(208) instead of "D" You can find these numbers when you click Insert > Symbols, then use the Webdings Font and look for the character # in the lower right I hope this helps and thanks so much.

[@JonathanExcels]

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

[@ExcelForFreelancers]

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.

[@-mahmoudfekry1174]

Thank you very much for the valuable information about Excel VBA :)

[@ExcelForFreelancers]

For sure, you are very welcome, I am happy to create and share these.

[@-mahmoudfekry1174]

@@ExcelForFreelancers Yes Video was shared via Facebook, Twitter and KZbin :)

[@ExcelForFreelancers]

Thank so much. I do appreciate that.

[@-mahmoudfekry1174]

@@ExcelForFreelancers I have a question in worksheet (1) in the data and I want to delete the repeated data in the rest of the other work papers by vba code How do I do this?

[@ExcelForFreelancers]

Hi thanks you can use a few different filtering techniques including advanced filter. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@siddheshtrivedi1796]

Hi Randy, Thank you for your amazing hard work and kind heart to share such informative knowledge will all of us. You make every complicated functionalities so easy. Kudos to you & your work! Your channel has helped me create & come up with an amazing tool for my organization. A thousand thanks are less! I have one query with regards to this functionality, When we open the file and instead of entering the UserID & PW, I cancel the login form, then the workbook gets unprotected. Can you help me to keep the workbook protected in case the Login form is cancelled. Thanks again! :)

[@ExcelForFreelancers]

Hi Siddhesh, thanks so much for your comment. I am really glad I can help and share my knowledge. Much appreciated.

[@siddheshtrivedi1796]

Thanks Randy, don’t know whether you read my query above, can you please help me out in the mentioned scenario! Appreciate your help! :)

[@ExcelForFreelancers]

You can write additional code to close the workbook on Form Deactivation to solve this problem. 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

[@muralidharan9576]

VERY USEFUL TUTORIAL...

[@ExcelForFreelancers]

Glad you think so and thanks so much Murali, Thank you for your Likes, Shares & Comments. It really helps.

[@seoagencybd]

really Amazing Sir,

[@ExcelForFreelancers]

Thank you so much. I am glad you like it.

[@pendoramariah]

WOW!! This is what I'm looking for😍

[@ExcelForFreelancers]

Perfect, I am very glad I could help

[@khacchinhnguyen7859]

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?

[@ExcelForFreelancers]

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

[@cancyheyliger8850]

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

[@ExcelForFreelancers]

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.

[@welgonzales4]

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.

[@ExcelForFreelancers]

Hi thanks so much Joel, that is a good suggestion and I do appreciate your feedback. Thanks again.

[@nejatbocnak1699]

I can not tell you my appreciation with words. All the best. Thank you very much.

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Nejat

[@nejatbocnak1699]

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

[@ExcelForFreelancers]

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

[@nejatbocnak1699]

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

[@nejatbocnak1699]

@@ExcelForFreelancers ı wrote my issue fcb group, but no return so far. do you have any other solution for me? thx. a lot.

[@mariusmatei4562]

Thanks a lot for this video!!!

[@ExcelForFreelancers]

For sure, you are very welcome I am happy to help

[@VoVietToan]

thank you Randy

[@ExcelForFreelancers]

For sure, you are very welcome. cảm ơn

[@ijnmpi]

Thanks a lot buddy! it helped :)

[@ExcelForFreelancers]

For sure, you are very welcome Ketan

[@justiceakoi4083]

This tutorial is good. Nevertheless two things have been left out. First, Admin to monitor logins and second restrict users based on number of times they have to login (with Date and time). If there could be an update to this video, it will be the best on youtube ratings on Excel VBA..

[@ExcelForFreelancers]

Hi thanks very much for your comments. There are probably 100 features i wanted to add, or could have added, however I had to limit the training a bit. Concerning the Login Dates & Times, I have this in both Change Long 1 & 2 videos here: Change Log 1: kzbin.info/www/bejne/bIXRqYOsgd-SapY Change Log 2: kzbin.info/www/bejne/nHaUZoptZ9uhe6c

[@TRC00020]

Perfect , excellent video

[@ExcelForFreelancers]

Thank you very much Thomas.

[@bcurr02]

Thank you. This was an awesome tutorial. I'm just wondering if it's possible to build on this to only show specific user data on any sheet when using slicers as the filter???? Any thoughts or ideas on how this could be achieved?

[@ExcelForFreelancers]

Hi Bryan, thanks that is a great question. I am sure it is possible using sliders and hidden rows/columns. I hope this helps. Should you have any additional questions, please join our Excel For Freelancers Facebook Group in which we have thousands of Excel experts who can guide and help you with your Excel Questions. Just post your detailed question, and upload your current workbook, or post detailed screenshots so we can get you the answers you need, fast. Please join here:bit.ly/groupexcel

[@atty5627]

@@ExcelForFreelancers hey May the Lord bless you guys, angels from the heavens, ive already joined the group! see you!

[@lyladrayson5680]

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!

[@ExcelForFreelancers]

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.

[@lyladrayson5680]

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

[@ExcelForFreelancers]

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" KZbin channel. This channel covers lots of VBA basics and would answer your questions above regarding VBA basics

[@lyladrayson5680]

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

[@ExcelForFreelancers]

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.

[@davidwentireland]

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.

[@ExcelForFreelancers]

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: Ikzbin.info/www/bejne/l3y5Zqabj9KceNU I hope this helps and thanks so much.

[@ulquiorra3504]

Thank you so much.

[@ExcelForFreelancers]

For sure, you are very welcome

[@johnjacobjinglehimerschmid3555]

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.

[@ExcelForFreelancers]

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.

[@learncomputer7725]

You are brilliant sir

[@ExcelForFreelancers]

Thank you very much. I do appreciate that.

[@jakejames9686]

Good afternoon Randy this is a fantastic video thank you. I have a question though. Is there a way that we can receive the same desired effect using the windows login automatically to log in. The above example works perfectly but my sheet has several users simultaneously working on it.

[@ExcelForFreelancers]

Hi Jake thanks so much for your comment. Yes you can have multiple users share and sync your macro-enabled workbook. This training will show you how: kzbin.info/www/bejne/l3y5Zqabj9KceNU

[@AnwarHossain-ro2sp]

It is really awesome....

[@ExcelForFreelancers]

Thanks so much. I do appreciate that. :)

[@matthewallgor5200]

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.

[@ExcelForFreelancers]

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.

[@tokelvin7130]

your tutorial is fantastic, I really love the script you show which I would like to apply it in my job. Not sure if you could share the script to me so I can learn and digest it more effectively? Thanks a lot in advance.

[@ExcelForFreelancers]

Hi Kelvin Thanks for your comment. Every one of my KZbin training contains a free workbook. You can access this workbook from the links in the description.

[@tokelvin7130]

Excel For Freelancers thanks , I have obtained it 👍🏻

[@karthikb.k2753]

Hi, Thanks a lot. Love your presentations. It will be very helpful for me. However, how could we force an users to open the workbook in macro enable mode? Also, I need to share this workbook with different users via email, will the macro will work and I would like to know what is the best way to share this workbook with users and work on a collaborative manner.

[@ExcelForFreelancers]

Hi Karthik, thank you for your comment. While we cannot force users to open the workbook in macro-enabled mode, what we can do is close all other sheets on previous close, leaving only one 'Start' sheet. This Start sheet will contain a macro to open up all other sheets. If macros are not enabled, then they cannot open the application to start the software. 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

[@nerenlakhraj5252]

Hi Randy ... awesome tutorial ... the link to get the workbook is not hyperlinked, please send me the workbook ... thank you again for your valuable lessons

[@ExcelForFreelancers]

Hi thanks so much for the comment and sorry about the broken link I just fixed it, here it is: Download Your FREE Workbooks From either of the following links: Using Your Email Here: bit.ly/UserLogin_WkBk With Facebook Messenger Here: bit.ly/UserLogin_FbDl

[@ziaajju]

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.

[@ExcelForFreelancers]

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: kzbin.info/www/bejne/nJCUeaaIbphja6s

[@rajakumarasamy4896]

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

[@ExcelForFreelancers]

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

[@narvik6136]

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

[@ExcelForFreelancers]

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.

[@Todotienesolucion8370]

Excellent channel

[@ExcelForFreelancers]

Thanks so much. I do appreciate that.

[@petekhauv2476]

Great tutorial, thank you! How do I share a macro workbook in OneDrive that won't disable the macro?

[@ExcelForFreelancers]

HI and thanks so much Pete. I created a specific training on how to share and sync your macro-enabled workbook here: kzbin.info/www/bejne/l3y5Zqabj9KceNU I hope this helps and thanks so much.

[@bayarahmed2436]

hey there, thanks a lot for sharing, very useful. I have an excel database of employees leave days balance, I want to be able to share the file with each employee ( upload to the cloud) with each employee having access to see only his/her balance using username - password, is this possible? thanks once again !

[@ExcelForFreelancers]

Hi in this case it would be best to use your main file to create excel-based reports for each employee that allow them to open up workbooks that contain ONLY their data. I have a course coming up in a few weeks that explains exactly how to create a dynamic report and export those details in which you will find very helpful. Make sure you join our group and email to stay tuned www.ExcelForFreelancers.com , bit.ly/groupexcel

[@purushottammandal5356]

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

[@ExcelForFreelancers]

Hi and thanks for your message. You can shar and sync your macro-enabled workbook using this training here: kzbin.info/www/bejne/l3y5Zqabj9KceNU I hope this helps and thanks so much.

[@sijusivan77]

YOU ARE AMAZING ❤️

[@ExcelForFreelancers]

Thank you so very much, I really appreciate that Siju.

[@tazz1691]

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.

[@ExcelForFreelancers]

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.

[@samnangchhing3333]

Dear respectful teacher, can many user log in and work on excel at the same time? is there any way to allow the users work on it at the same time?

[@ExcelForFreelancers]

Microsoft Excel does have a 'Shared Workbook' option in which multiple users can use a workbook at the same time. It is not a perfect solution but it does work on most workbooks.

[@tunaungkyu9828]

Thank you Sir

[@ExcelForFreelancers]

For sure, you are very welcome Tun

[@jakedelarosa2790]

Great video. Will this work if the file is located in Sharepoint or Onedrive? Looking forward to reading your reply.

[@ExcelForFreelancers]

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

[@garyjohn8297]

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") = "Ð"

[@ExcelForFreelancers]

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!

[@garyjohn8297]

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.

[@ExcelForFreelancers]

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.

[@tramnguyenlequynh5702]

excellent design & coding.

[@ExcelForFreelancers]

Thank you very much. I do appreciate that Khoi. Cam on em :)

[@user-cr4vx2qf4u]

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

[@ExcelForFreelancers]

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.