Password Protect & Hide Worksheets in Excel
HTML-код
- Опубликовано: 15 окт 2024
- VBA/Macros Course (80% Discount): www.teachexcel...
Excel File: www.teachexcel...
Hide Password Characters: • Make a Password Prompt...
Slicer Power Analysis in Excel: • Slicer Power Analysis ...
This works by showing the user a password input window and, if they input the correct password, they can view the hidden worksheets, otherwise the worksheets stay hidden and cannot be viewed.
This adds a nice user-interface and data management solution to your workbook and I show you how to apply it to any workbook.
The hidden worksheets in this tutorial cannot be viewed by right-clicking a sheet tab and clicking Unhide and they cannot be viewed by manipulating the project itself, because that will also be protected.
Excel Courses:
VBA/Macro Course: www.teachexcel...
Building Professional Forms: www.teachexcel...
TeachExcel.com
#msexcel #howto #tutorial
![ARGENTINA vs. BOLIVIA [6-0] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 10](http://i.ytimg.com/vi/C3wPtKNbAOA/mqdefault.jpg)
I did a project many years ago, and I set up password on some behind scene sheets. But then I lost the project, disks, etc. And could not remember all the steps. You just revived my memory. You are my STAR!
Very usefull video. Thank you for sharing. Used to write code with vb but if you don't use that staff regularly it's easily forgotten. It would be useful to provide the steps in creating the buttons. Also useful to hide the sheet automatically upon closing the file because if you save with the sheet unhiden it will be visible to anyone who opens the file next. Did some messing around with chatgpt which was unable to provide solid code for this to work after a couple of hours of trial and error. However, I copied a piece of code for hiding the sheet upon closing the file and added it to your code. Works with no issues.
This is the code I used if it is helpfull yo anyone. Go to developer tab and then add the following to "this workbook" object in the project explorer:
Private Sub Workbook_Open()
' Ensure the sheet "sheet name" is hidden when the workbook opens
Dim ws As Worksheet
Set ws = Me.Sheets(“sheet name")
ws.Visible = xlSheetVeryHidden
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Ensure the sheet is hidden before the workbook closes
Call HideSheet
End Sub
Very helpful
This was a really solid VBA tutorial, you were great at breaking it down step by step, explaining everything along the way.
Thank you for this video, it's really useful and detailed. I have one question... Is there any way that the spreadsheet will always open with hidden tabs? When I unhide all my tabs and save it then next time when I open the spreadsheet it shows unhidden tabs which everyone can have access to. Thank you.
This is such a great teaching video. I love the power of Excel and from time to time have written a few engineering or science-based tools with Excel in the past for my work. I'm now on a mission instead and using Excel to code a critical calculation tool that I'll release this month. I wish to show only some worksheets and keep others unavailable. You've done a great job with your method and teaching style. 🏆 💯 Thank you so much! 😃 (Rodney, a very happy student of TeachExcel)
This is brilliant. Thank you a lot for such a proper explanation, and for the code, of course!
thank you, that was great. however is their anyway to mask the password
Clear video. Is it still possible to enter data in the hidden sheets with a reference from another sheet?
Cells in the hidden sheet can still be referenced
There is one issue, if we save the workbook without hide the related sheet, how can we set the sheet be automatically hidden whenever the workbook open ?
Thank you so much this is what been looking for.. however I am not able to do this as there is an error "Compile Error : Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules". Can you help on this? Thank you
Excellent tool. thank you. I have a large spreadsheet with sheet names that contain spaces, e.g. "Sheet Name" instead of "SheetName", and running this code gives me error 9 "Subscript out of range". I can't change the sheet names unfortunately. Is there a way to fix this?
Nice course! Can someone use the password from a cellvalue that can be changed without going back in VBA? Thanks!
Excellent Tutorial! Very well explained, which made it easy to use. I have a question. Can multiple worksheets be protected with different Passwords?
Yes they can. Where he sets his password "123456", make second just like it saying "654321". If inputbox blah blah = "123456" unhide one sheet. Else If input box = "654321" unhide the other sheet.
Thank you dear... it solved a major problem for me... really nice video tutorial... can't get better...
Many many thanks for the tutorial
How to hide by default on exit ?
I would like to know this as well.
excellent. Very handy method to keep the inner workings of a sheet away from prying eyes
It worked, thanks for the video. Wish I would have found this years ago.
Nice! We need more positive examples of how useful vba can be.
Thanks) I will try to get some more practical examples out there that are easy to digest :)
Great tutorial. Only question I have - would it be possible to lock each worksheet with a different password so that only when a user enters that specific password, then only the sheet the password belongs to opens up. Still hiding the other sheets so that the user only works on their department sheet, updating the master sheet and then saves and exit.
Is that possible on VBA?
Bump.
This is why I'm here too
Think we can tweak this and to two separate one for each button? I'll give it a try.
This can work When one uses number of modules equal to number of the sheets you are hiding.
Keep the videos coming. These are super helpful!
THANK YOU BRO YOU CHANGED MY LIFE COURSE
😍😍😍
Very helpful. thank you. The code was hidden for a minute until I tiled the windows. ;)
Is this possible when you disable macro the hide sheet can be seen?
Hi, this is exactly what i needed but I am having some trouble getting it to save correctly. I have the file saved as "Excel Macros-Enabled worksheet" but everytime i close out and re open the file the pw protection on the very hidden sheet's vba coding gets erased and can be unhidden when you hit alt+F11. I have tried to save it a couple different formats and it's still the same. Any ideas?
How to create a template like this with multiple worksheets needed an individual password
Awesome! very detailed and easy to understand.
Unable to see the code on the module window in the VBA any suggestions?
Is not possible to create a new user in your website.
Can you guide me how to hide code from module like excel file that i download from your link. I cant see any thing from it. Thank you very much!
Cool Tutorial,Great Way For Protecting/Hiding Data Worksheets With Some Simple Neat VBA Code.Great Tip Also For Locking Project...Thank You Sir :)
Thanks Darryl! I'm glad you think so :) I think too many people forget that xlVeryHidden means nothing if the project is viewable lol.
Is there a way I can do this with multiple passwords that unlock different sheets dependent on the password?
Figured this out. Change to conSheetPassword 1 = 123456, then next line add Public Const conSheetPassword2 As String...
A great tutorial. Thank you very much!
You are very welcome Ivan! :)
Compile Error: Constants, fixed-lengh strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules
Thank you Very much!!! This is Great.
Thank you for teaching .
I keep getting 'Sub or Function not defined' any idea?
This is brilliant!!!
I cannot open this file with the macros. Does anyone just have the code?
I am getting an error on the following string: Public Const conSheetPassword As String. Any suggestion?
I had the same problem, you need to insert a new modul on the left and write your code there
This is great! I am running into a problem though - If I do the last step and lock the project with a password it disables all macros in the workbook the next time I open the workbook and I am unable to use the hide/unhide buttons anymore
hi, how did you made a module 1? i tried alt+f11. but i dont have module 1 to make commands. could you please advise
To add a module, you have to right click the vba project and then insert and then Module...
It is really good. However I want to know how to hide what password we entered in pop-up dialog windows
Thanks for the comment :) I made a new tutorial on that and the link has now been added to the description of the video.
Here it is for your convenience: ruclips.net/video/Y2k3uAM6N-A/видео.html
@@TeachExcelI watched the other video but it does not show you how to swap the user form with the input box or make them work together. Please advise for those of us that are not as familiar with VBA.
Nice! Thanks for sharing :)) Thumbs up!!
Thanks Wayne!!!
Thank you 🖤 you're amazing
Thank you that was very helpful..
Helpfull ... Thank you very much!
Great video. Thank you.
I click on the link but it will not let me download the file - please help
You need to register with your email to get the files now. (Its free)
But anyone can just open VBA and see the PW... I need to hide a few tabs but still allow people to work unrestricted on the rest of the workbook
thanks for sharing, great
Tanks sir
You are very welcome :)
Great
I will say that its not protected anyone can go to the VBA and See the password from there...😒😒
He addresses this in the end of the video