Wow! I just love the spirit of this community. Always sharing, always recognising each other's talents and contribution. Came for the training, stayed for the comradery. "Amid whiffs of truffle oil and warm maple syrup, there was an overall air of comradery; a glamorous village held together by their shared interest in a humblingly good cause. " - Carmen Rosy Hall, Vogue. Feb 2019
Thank you Daniel & Leila for sharing with us. You just made my working life so much more easy and beautiful!!! You are a blessing for the Excel Users community. I have no words!!!
Thanks Leila for your interest in this utility and the great presentation. I certainly could not have done better. The post goes into some details but, as you clearly explained, the most important thing is to copy it in the PERSONAL.XLSB workbook. That was very well demonstrated. Thanks!!! B-)
It's far easier to make it an xlam in my opinion. Xlam files can be installed automatically with workbook_open(), meaning the user only opens the file once and it's installed and fully functional.
@@TheHellis Hello TheHellis. Thanks for your post. We do not want to go beyond the purpose and intention of this utility. It was not about how to program an add-in. It is all about the utility and not about the extension. Solving an annoyance with Excel rather than boasting about my programing skills. I wish that whoever is interested can look into the code and learn. A PDF was provided. Using the PERSONAL.XLSB works very well. Thanks.
@@DanielLamarche Sure. But I'm just saying it's a very user unfriendly way. You can choose whatever way you want. I find xlam and autoinstall and autoupdate a better way to solve the problem, especially in a corporate enviroment. Anyone who wants to use a autoinstalling and autoupdating file can download this sample. Download it to the desktop and run it from there. It can't be opened from browser or mail as that is a temporary folder. Doubble click on the file to install the "Hello World" macro. www.hoppvader.nu/excelbild/AutoInstall.xlam
Leila you are indeed very inspiring.. the fact that you gave all the credit to Daniel here shows your heart is in the right place, pls keep the good work coming in.. Daniel you are a super star!
Thank you for sharing the knowledge with us Leila. I just wanted to share that we can directly hide or unhide any sheet. We just need to right click on any sheet and we will see the hide unhide option.
@@DanielLamarche You are welcome! I am glad that the PQ and DAX material is useful!!! You are not going to MVP summit in Seattle? I can't go either, even though I live in Seattle. I am sad to miss this epic event : (
@@excelisfun You are such a friendly mate Michael. You know, I'm a bit like you when I teach Excel, when I'm excited with a feature I do short noise like Pow!, Taff! Bingo!!! Loll Thanks for all bro!!!
Madam, the flow of your presentation is so beautiful, I can't stop myself to praise you, though I don't like excel but still I subscribed your video, beautiful madam.😊
Name-checked in a Leila-video. Made my day! Nice work Leila and Daniel. I wish I had spotted the utility before writing my long-winded answer on the original video, so I could have just provided a link!
Hi Leila.. thanks for sharing this tip and tool. When viewing some of the lessons inside your VBA course, I was thinking about setting up something like this. I have some code that I use to hide any tabs with a certain color and then also code to unhide all hidden sheets. I put buttons on the QAT to hide/unhide on those criteria. Your user form example gives more control and power over individual sheets vs. the grouped method I'm using. I can think of other functionality that might be useful in a user form. Thanks for the sharing and inspiration.. always insightful and thought provoking. Thumbs up!
Hi Daniel.. thanks.. I'm learning user forms now through a combination of resources including Leila's VBA course. Let me tinker and I'll share back what I come up with or reach out for advice. Thanks again for your generosity in sharing your utility.. great for both learning and using. Cheers!
Brilliant! Will definitely be using this. Does it handle only hidden sheets or also xlveryhidden sheets? Thanks! Update: did you edit the code? Daniels version doesn’t recognise using shift and ctrl for selecting multiple sheets?
Thanks Leila, your videos are great help. My work sometime requires me to move through different workbooks, so I keep most of them open or hidden. I request you to help me unhide workbooks all at once or any utility like this one to unhide a list of workbooks those are hidden.
Hi Leila, Thank you sharing. I like to mention that Jon Acompora got similar utility with more features like visible, hidden and very hidden drop down list in form. Choose option and see which sheet is visible or hidden or very hidden. Below in that form different choices like hide, unhide and very hidden sheets with multiple selection. Very useful 👍👍👍. Hoping you will also like it 😃😃😃 Regards
Thanks Daniel and Laila for your great effort to build these kind of utilities free for use. This is a release of a huge burden to most of Excel users...
Thanks Nipun. You are totally right. For me hiding multiple sheets is a common tasks. I've worked once with a lady that had more than 200 worksheets in her workbook!!! Imagine how my utility was useful!!!
Hi Leila; Thanks for a wonderful utility. But somehow I feel, this thing is like using a cannon to kill a rabbit. The simple way is ALT+O+H+U (sheet un-hide), select sheet from the drop list and press ENT. For hiding sheets, use CTRL + mouse to select the sheets and press ALT+O+H+H. Once the user by heart the key strokes it will become cake walk.
Hello Sachin. You're the second member that suggests this solution. Please remember that the utility is NOT about HIDING worksheets but UNHIDING worksheets. The solution you suggest (Alt, O, H, U) will indeed open the Unhide dialog box but it will only allow the user to unhide ONE sheet at a time. This was the purpose of the utility as it's name suggest: Unhide Sheets. Hope this reply makes it clearer. Thanks for your comment.
Hi Sachin - thank you for the shortcut suggestions - this is also about unhiding many sheets all at once. With the usual Excel interface you can do one at a time. This utility will be useful for those who need to unhide a lot on a frequent basis.
That was a complex way of doing it. I make the files a xlam file and add my "install code" which means if you open the xlam you get the option to install the code. That way I can just send a file to a colleague and they (hopefully) can install it themselves. I also have code that can update the code if I make a new version. A auto update of xlam files. Makes it so much easier to administrate VBA code in a work environment where most have no clue.
To make personal.xlsb you have to turn on the recorder and turn it off with personal xlsb selected - I show that at the beginning of the video. You can also read through the instructions in the PDF.
Dear Leila, I have followed your instructions and download the folder. However, when I opened the VBA sheet, I didn't find FORM folder unfortunatelly. I guess this folder is not reached anymore.
I have an excel 2019 workbook that has tabs linked to the main sheet. I have been successful in hiding unused portions of the main page however now I am coming up with an error "Cant Push Objects off sheet" when I try to hide rows. Also to the right of the main sheet I have many columns I don't use so I've successfully hid them and below the data of the main sheet I've been able to hid that unused area. So why can't I now hid and data in the middle of the main sheet without getting this error??? Any thoughts would be helpful
Help me for vba Column a is empty my entries are in b,c,d,e,f....and if I write sold in column a in any cell then that row cuts and gets paste in the end of sheet2 Thanks
hey i have a question, i have a column with weeks like 31/12/2018 - 7/1/2019 - 14/1/2019 and so on now i want to get the month from those weeks the problem is some weeks start with 1 or 2 or 3 from one month and the rest in other month like the first week in the year it start with 31/12/2018 and gets month december and i want it to be january, what i want is if the start or end week have more than or equal to 4 days in the new month then it should consider it in the new month and if it has less than or equal to 3 days then it should consider it in the previous month, i hope you understand what i mean, plzzzzzz i need this
Hi Mohamed. If your list of dates begins in column A1, type the following formula into B1 and copy down =MONTH($A1)+(-($A1-EOMONTH($A1,-1)-WEEKDAY($A1)
By the way, watching your video I have been wondering how you 'smoothly' zoom in your screen while recording. Window built-in magnifier(win key, + key) does not zoom smoothly. Can you please share the knowledge as well if you don't mind?
This is a cool macro, but doesn't this functionality already exist in Excel? Alt+O+H+U (legacy command, Alt+H+O+U+H in the current version), then select the sheets to unhide. If you want an icon to click on, Excel 365 also has a built-in unhide sheets shortcut you can add to the Ribbon or Quick Access Toolbar, from the All Commands list.
@@texicaliblues Hello texicaliblues. Daniel here (author of this utility). The command you are refering here is exactly the same as right-clicking on any sheet then choose Unhide. So no need to remember a series of menu letters. However as the article points out, the command you are referring to can only unhide ONE sheet at a time!! This is the limitation I explain in my post. The utility Leila is featuring allows the user to use the Ctrl or Shift key to select multiple sheets and unhide them in one operation. There is even a Unhide All button. I tested it with 80 hidden sheets and it did the job in 1/10th of a second!
@@DanielLamarche Gotcha. Thanks for the clarification. The built-in functionality has never been a limitation for me. All depends on the use cases we encounter, I guess.
@@texicaliblues Totally agree. Occasionally I'm asked a question re this limitation and when I show them the utility they are thrilled!!! Thanks for getting back to me.
Good news! If you have Office 365 you can now unhide multiple sheets at once! YAY!
One for Daniel👍 and one for Leila 👍.Thanks both of you
Thank you for the double thumbs up :)
Thank you Leila, you are the undisputed Excel Queen! And that Daniel is my friend as of today :-)
Wow! I just love the spirit of this community. Always sharing, always recognising each other's talents and contribution. Came for the training, stayed for the comradery.
"Amid whiffs of truffle oil and warm maple syrup, there was an overall air of comradery; a glamorous village held together by their shared interest in a humblingly good cause. "
- Carmen Rosy Hall, Vogue. Feb 2019
I love that quote Roderick! Thank you for sharing & thank you for staying.
Oh my God. This is definitely not just a time-saver but a life-saver. Thank you so much Daniel and Leila
We are glad it's helpful.
Thank you Daniel & Leila for sharing with us. You just made my working life so much more easy and beautiful!!! You are a blessing for the Excel Users community. I have no words!!!
Thank you Antonios for your support. We are happy to heat that!
Thanks Leila for your interest in this utility and the great presentation. I certainly could not have done better. The post goes into some details but, as you clearly explained, the most important thing is to copy it in the PERSONAL.XLSB workbook. That was very well demonstrated.
Thanks!!! B-)
It's far easier to make it an xlam in my opinion.
Xlam files can be installed automatically with workbook_open(), meaning the user only opens the file once and it's installed and fully functional.
I'm glad you like the presentation :) Thank you for sharing your utility with us.
@Edwin Teulon Great! Now spread the joy! Loll
@@TheHellis Hello TheHellis. Thanks for your post. We do not want to go beyond the purpose and intention of this utility. It was not about how to program an add-in. It is all about the utility and not about the extension. Solving an annoyance with Excel rather than boasting about my programing skills. I wish that whoever is interested can look into the code and learn. A PDF was provided. Using the PERSONAL.XLSB works very well. Thanks.
@@DanielLamarche
Sure.
But I'm just saying it's a very user unfriendly way. You can choose whatever way you want.
I find xlam and autoinstall and autoupdate a better way to solve the problem, especially in a corporate enviroment.
Anyone who wants to use a autoinstalling and autoupdating file can download this sample.
Download it to the desktop and run it from there. It can't be opened from browser or mail as that is a temporary folder.
Doubble click on the file to install the "Hello World" macro.
www.hoppvader.nu/excelbild/AutoInstall.xlam
Leila you are indeed very inspiring.. the fact that you gave all the credit to Daniel here shows your heart is in the right place, pls keep the good work coming in.. Daniel you are a super star!
Thank you for sharing the knowledge with us Leila.
I just wanted to share that we can directly hide or unhide any sheet. We just need to right click on any sheet and we will see the hide unhide option.
Yes, but you can only unhide one sheet at the time. With the tool you can unhide multiple sheets at the same time.
Go Team!!! Thanks Leila and Daniel!!!!!
Thank you Teammate Mike for your support :)
Hey Mike! We meet again but not in Seattle unfortunately. I am such a fan of your Power Query & DAX material!
Thanks for comment too bro...
@@DanielLamarche You are welcome! I am glad that the PQ and DAX material is useful!!! You are not going to MVP summit in Seattle? I can't go either, even though I live in Seattle. I am sad to miss this epic event : (
@@excelisfun You are such a friendly mate Michael. You know, I'm a bit like you when I teach Excel, when I'm excited with a feature I do short noise like Pow!, Taff! Bingo!!! Loll
Thanks for all bro!!!
@@DanielLamarche Pow!!! Go Team!!!!!!
Thanks you Daniel and leila . We are so grateful to you
Thank you Daniel and Leila for sharing this!
Thank you Leila and Danial for great work ...Stay blessed....
Thank you Daniel and Leila so much
Our pleasure :)
Madam, the flow of your presentation is so beautiful, I can't stop myself to praise you, though I don't like excel but still I subscribed your video, beautiful madam.😊
Thanks Laila & Daniel very useful information
Glad you find the tutorial useful.
Name-checked in a Leila-video. Made my day! Nice work Leila and Daniel. I wish I had spotted the utility before writing my long-winded answer on the original video, so I could have just provided a link!
Thank you Leila for sharing Daniels work. It is very convenient. Thank you Daniel.
Dear LEILA your channel very helpful it makes Excel easy, we learn many thing from you.
Great Job Leila and Daniel!!!! THANKS EXCEL TEAM Very Handy and Useful tips!!!!!!!!!
Thank you Daniel and Leila! Very useful indeed :)
Our pleasure :)
Great spirit and community!
Agree - The community is great :)
Great tool !!! Thanks Leila & Daniel !!!
Thank you Daniel and Leila so much, this is so helpful for productivity at my job!
Thank you Leila and Daniel! I’m going to add that to my ribbon this morning.
And congratulations on reaching 100k subscribers - not at all surprising!
Thank you Andy!
Thanks Daniel and Leila for doing a Video to share it :) :)
You're very welcome John.
Hi Leila.. thanks for sharing this tip and tool. When viewing some of the lessons inside your VBA course, I was thinking about setting up something like this. I have some code that I use to hide any tabs with a certain color and then also code to unhide all hidden sheets. I put buttons on the QAT to hide/unhide on those criteria. Your user form example gives more control and power over individual sheets vs. the grouped method I'm using. I can think of other functionality that might be useful in a user form. Thanks for the sharing and inspiration.. always insightful and thought provoking. Thumbs up!
Thank you Wayne for your comment. Agree - we can use the userform and add extra features to it as we need. Makes it more fun :)
Hi Wayne. Tell me what you have in mind and ... who knows!!!
Hi Daniel.. thanks.. I'm learning user forms now through a combination of resources including Leila's VBA course. Let me tinker and I'll share back what I come up with or reach out for advice. Thanks again for your generosity in sharing your utility.. great for both learning and using. Cheers!
Thank you Daniel and Leila
Omg!! This made my life easy 😀
Thank you Daniel and Leila ♥️
You're very welcome.
Thank you Leila.. you have been my guru (teacher) in excel.. one request to you.. please make a video on cube formula in excel. Thanks
Thank you Daniel.. I have so much to learn!
Perfect! It will be very useful. Thanks to you & Daniel.
You're very welcome Disha.
Thanks Disha. If you visit my Web site in about a month there will be another goodie. Maybe even more useful than the one above!
Looking forward to it.
Thanks for sharing Liela, very useful.
Beautiful. Thank you both!!
Great video!!!! Good night
Thanks Leila, thanks Daniel
This is great! Thanks for the explanation,Leila!
You're very welcome Wim.
Very clear , thanks !
Awesome! Thanks to both of you!
You're very welcome Elías!
Brilliant! Will definitely be using this. Does it handle only hidden sheets or also xlveryhidden sheets?
Thanks!
Update: did you edit the code? Daniels version doesn’t recognise using shift and ctrl for selecting multiple sheets?
Thanks once again for more "Miracle" Now just waiting for the next 👍
Really cool! Is there a similar quick utility option to unhide rows and columns?
Thanks Leila, your videos are great help. My work sometime requires me to move through different workbooks, so I keep most of them open or hidden. I request you to help me unhide workbooks all at once or any utility like this one to unhide a list of workbooks those are hidden.
Thanks Daniel!
Hi Leila,
Thank you sharing.
I like to mention that Jon Acompora got similar utility with more features like visible, hidden and very hidden drop down list in form. Choose option and see which sheet is visible or hidden or very hidden. Below in that form different choices like hide, unhide and very hidden sheets with multiple selection. Very useful 👍👍👍.
Hoping you will also like it 😃😃😃
Regards
That sounds great! Thank you for sharing.
@@LeilaGharani ruclips.net/video/LNFCbKAk1Gk/видео.html you will like it...
Thanks Daniel and Laila for your great effort to build these kind of utilities free for use. This is a release of a huge burden to most of Excel users...
Thanks Nipun. You are totally right. For me hiding multiple sheets is a common tasks. I've worked once with a lady that had more than 200 worksheets in her workbook!!! Imagine how my utility was useful!!!
@@DanielLamarche Will keep in touch for more updates. Thanks...
Hi Leila;
Thanks for a wonderful utility. But somehow I feel, this thing is like using a cannon to kill a rabbit. The simple way is ALT+O+H+U (sheet un-hide), select sheet from the drop list and press ENT. For hiding sheets, use CTRL + mouse to select the sheets and press ALT+O+H+H. Once the user by heart the key strokes it will become cake walk.
Hello Sachin. You're the second member that suggests this solution. Please remember that the utility is NOT about HIDING worksheets but UNHIDING worksheets. The solution you suggest (Alt, O, H, U) will indeed open the Unhide dialog box but it will only allow the user to unhide ONE sheet at a time. This was the purpose of the utility as it's name suggest: Unhide Sheets. Hope this reply makes it clearer. Thanks for your comment.
Hi Sachin - thank you for the shortcut suggestions - this is also about unhiding many sheets all at once. With the usual Excel interface you can do one at a time. This utility will be useful for those who need to unhide a lot on a frequent basis.
How did you record your screen and face simultaneously? Did you record you face using a camera and later you embeded with your screen recorded video?
Thanks for sharing
Nice and clear.
Thank you Daniel VIA Leila😋
Thanks you all.
thanks a lot for this.
You're very welcome.
Ty, and hope you're doing well ❤❤
Thanks !!! Appreciated !
Hi Leila, could you tell me which program you use to make your videos. Grettings
I mainly use Camtasia. You can check out my gear here: www.xelplus.com/resources/#gear
So sweet trick
Awesome Mam
That was a complex way of doing it.
I make the files a xlam file and add my "install code" which means if you open the xlam you get the option to install the code.
That way I can just send a file to a colleague and they (hopefully) can install it themselves.
I also have code that can update the code if I make a new version. A auto update of xlam files.
Makes it so much easier to administrate VBA code in a work environment where most have no clue.
Thanks
That's awesome
thanks..
Kind explaination on how to make PERSONAL.xlsb and how to manuver between vba modules.
To make personal.xlsb you have to turn on the recorder and turn it off with personal xlsb selected - I show that at the beginning of the video. You can also read through the instructions in the PDF.
@@LeilaGharani I meant your video so kindly explained them.^^ Thanks twice.
Hi leila, i have created the personal workbook but its showing run-time error 13 while un-hide
Thank i will try New command mem.......
Dear Leila,
I have followed your instructions and download the folder. However, when I opened the VBA sheet, I didn't find FORM folder unfortunatelly. I guess this folder is not reached anymore.
Best would be to leave a comment on Daniel's blog post.
I have an excel 2019 workbook that has tabs linked to the main sheet. I have been successful in hiding unused portions of the main page however now I am coming up with an error "Cant Push Objects off sheet" when I try to hide rows. Also to the right of the main sheet I have many columns I don't use so I've successfully hid them and below the data of the main sheet I've been able to hid that unused area. So why can't I now hid and data in the middle of the main sheet without getting this error??? Any thoughts would be helpful
Help me for vba
Column a is empty my entries are in b,c,d,e,f....and if I write sold in column a in any cell then that row cuts and gets paste in the end of sheet2
Thanks
love from pakistan
hey i have a question, i have a column with weeks like 31/12/2018 - 7/1/2019 - 14/1/2019 and so on now i want to get the month from those weeks the problem is some weeks start with 1 or 2 or 3 from one month and the rest in other month like the first week in the year it start with 31/12/2018 and gets month december and i want it to be january, what i want is if the start or end week have more than or equal to 4 days in the new month then it should consider it in the new month and if it has less than or equal to 3 days then it should consider it in the previous month, i hope you understand what i mean, plzzzzzz i need this
Hi Mohamed. If your list of dates begins in column A1, type the following formula into B1 and copy down
=MONTH($A1)+(-($A1-EOMONTH($A1,-1)-WEEKDAY($A1)
By the way, watching your video I have been wondering how you 'smoothly' zoom in your screen while recording. Window built-in magnifier(win key, + key) does not zoom smoothly. Can you please share the knowledge as well if you don't mind?
The zooming is done with Camtasia - the screen-recording software.
@@LeilaGharani Aha, Camtasia! I have heard of it. Thanks a lot for sharing your tip.
Madam......can you please make vedio on power quiriy ...how to use it ....how to combine large data base... please
Thanks.
Take care.
It is definitely on the list for future videos.
@@LeilaGharani
Thank u very much madam..
God bless you....tc
U doing wonderful job....
Nice
Good
This is a cool macro, but doesn't this functionality already exist in Excel? Alt+O+H+U (legacy command, Alt+H+O+U+H in the current version), then select the sheets to unhide. If you want an icon to click on, Excel 365 also has a built-in unhide sheets shortcut you can add to the Ribbon or Quick Access Toolbar, from the All Commands list.
Alt, O, H, U and Alt, H, O, U, H is probably a better way to write it. Was thinking that the plus signs might indicate simultaneous key presses.
@@texicaliblues Hello texicaliblues. Daniel here (author of this utility). The command you are refering here is exactly the same as right-clicking on any sheet then choose Unhide. So no need to remember a series of menu letters. However as the article points out, the command you are referring to can only unhide ONE sheet at a time!! This is the limitation I explain in my post. The utility Leila is featuring allows the user to use the Ctrl or Shift key to select multiple sheets and unhide them in one operation. There is even a Unhide All button. I tested it with 80 hidden sheets and it did the job in 1/10th of a second!
@@DanielLamarche Gotcha. Thanks for the clarification. The built-in functionality has never been a limitation for me. All depends on the use cases we encounter, I guess.
@@texicaliblues Totally agree. Occasionally I'm asked a question re this limitation and when I show them the utility they are thrilled!!!
Thanks for getting back to me.
I need to know the color code of text in pdf
Thanks leila and Daniel
How can I get free excel 365?
You're very welcome. 365 is a yearly subscription though.
Slick
Leila Is so beautiful.
سلام
لیلا خانم چطور می توان آفیس ۳۶۵ رو رایگان دانلود کرد؟
Hughhhh .... You're welcome??? 8-}
❤️❤️❤️❤️