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
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 ?
Hallo, this video was very amazing and helpful on my Workbook. But I have some question, what if i want to show ****** when typing the password?. Looking for your response. Thank you so much.
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
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.
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?
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?
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?
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
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)
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.
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: th-cam.com/video/Y2k3uAM6N-A/w-d-xo.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.
@@Lady112017 hi, I would like to ask if your question about this was already been answered? would you mine, share to me how it works? I'm also watching the video and i don't know how to integrate it to the existing code. Thank you.
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!
This was a really solid VBA tutorial, you were great at breaking it down step by step, explaining everything along the way.
Many many thanks for the tutorial
How to hide by default on exit ?
I would like to know this as well.
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 is brilliant. Thank you a lot for such a proper explanation, and for the code, of course!
Awesome! very detailed and easy to understand.
It worked, thanks for the video. Wish I would have found this years ago.
Thank you dear... it solved a major problem for me... really nice video tutorial... can't get better...
Keep the videos coming. These are super helpful!
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 :)
excellent. Very handy method to keep the inner workings of a sheet away from prying eyes
thank you, that was great. however is their anyway to mask the password
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 ?
Hallo, this video was very amazing and helpful on my Workbook. But I have some question, what if i want to show ****** when typing the password?. Looking for your response. Thank you so much.
Thank you for teaching .
Nice course! Can someone use the password from a cellvalue that can be changed without going back in VBA? Thanks!
Thank you Very much!!! This is Great.
This is brilliant!!!
Very helpful. thank you. The code was hidden for a minute until I tiled the windows. ;)
A great tutorial. Thank you very much!
You are very welcome Ivan! :)
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
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
Thank you that was very helpful..
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.
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?
Great video. Thank you.
Helpfull ... Thank you very much!
Is this possible when you disable macro the hide sheet can be seen?
Thank you 🖤 you're amazing
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?
Nice! Thanks for sharing :)) Thumbs up!!
Thanks Wayne!!!
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...
How to create a template like this with multiple worksheets needed an individual password
THANK YOU BRO YOU CHANGED MY LIFE COURSE
😍😍😍
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!
thanks for sharing, great
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.
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.
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
Unable to see the code on the module window in the VBA any suggestions?
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)
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.
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: th-cam.com/video/Y2k3uAM6N-A/w-d-xo.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.
@@Lady112017 hi, I would like to ask if your question about this was already been answered? would you mine, share to me how it works? I'm also watching the video and i don't know how to integrate it to the existing code. Thank you.
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...
I keep getting 'Sub or Function not defined' any idea?
I cannot open this file with the macros. Does anyone just have the code?
Compile Error: Constants, fixed-lengh strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules
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
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
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