Excel Macros & VBA: 4 Introduction to the VBA Editor and hiding worksheets with the visible property

// FREE Excel E-book "Record Your First Macro"
→ www.launchexcel.com/record-yo...
// Recommended Excel Courses //
1. Launch Excel Macros & VBA School: go.launchexcel.com/macros-vba...
2. Excel Campus Elevate Program: go.launchexcel.com/excel-camp...
3. My Online Training Hub Dashboards: go.launchexcel.com/moth-dashb...
4. Coursera Excel Specialisation: go.launchexcel.com/coursera-e...
5. Coursera Everyday Excel: go.launchexcel.com/coursera-e...
6. Coursera Excel Data Visualisation: go.launchexcel.com/coursera-d...
// Recommended Excel Templates //
If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.
go.launchexcel.com/simple-she...
They have Excel templates covering cash flow planning, invoices, expense reports, personal budgets, and more!
--
// EPISODE NOTES
In episode 4 I will:
▸ Introduce you to 6 parts of the VBA Editor (VBA = Visual Basic for Applications)
▸ Show you how to make sheets visible, hidden and VERY hidden (cannot be unhidden from normal Excel interface)
▸ Walk through a simple macro to Toggle sheets between visible and hidden
▸ You will learn how easy it is to start writing Macros to automate Microsoft Excel.
--
// LEARN MACROS WITH LAUNCH EXCEL
Most Excel users never start using Macros because they either haven't heard of them, or feel overwhelmed because they think it's all too hard. This is a shame because Macros and VBA can give you Excel superpowers.
You can save vast amounts of time by automating repetitive tasks and even create your own new functionality to do things that Excel does not do by itself.
But... it sometimes looks like Excel Macros are so technical. Don't worry, in this video I show you that the VBA Editor is a good tool for writing and testing code in Excel. And we have fun with hiding and unhiding worksheets. Cool!
--
// VISIT MY WEBSITE
For more awesome tutorials on Microsoft Excel visit my website → www.launchexcel.com
- - - - - - - - - -
Extra Notes
- - - - - - - - - -
1. You can access the VBA Editor using the shortcut key combination ALT + F11
2. To step line-by-line through your macros in the VBA Editor use the keyboard shortcut F8
3. Here is the sample code for the macros to toggle worksheets between visible and hidden. It's here for entertainment purposes only. Please use reasonably, and remember to keep backups of your documents before you run macros on them.
You need to copy and paste these into one code module, and you can assign the sub ToggleHide() to a Form Control Button in one of your workbooks. Remember to change the sheet names to ones in your own workbook (Sheet1, Sheet2, Sheet3)
Remember it's a good idea to comment your code using the apostrophe (‘) at the start of a line or middle of a line. Excel will ignore everything after the apostrophe (‘) on any given line.
------------------------------------------------------------------
Sub ToggleHide()
'ASSIGNED: Form control button on worksheet "Visible"
'ACTION: Toggles worksheet visibility by showing and hiding sheets
If Sheet3.Visible = xlSheetVisible Then
Call HideSheets
Else
Call ShowSheets
End If
Sheet1.Activate
End Sub
------------------------------------------------------------------
Sub HideSheets()
'Set Sheet1 to VISIBLE
'Set Sheet2 to HIDDEN - it can still be unhidden from Excel UI
'Set Sheet3 to VERY HIDDEN - it cannot be unhidden from Excel UI
Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetHidden
Sheet3.Visible = xlSheetVeryHidden
End Sub
------------------------------------------------------------------
Sub ShowSheets()
'Make Sheet1, Sheet2, Sheet3 visible
Sheet1.Visible = xlSheetVisible
Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVisible
End Sub

[@launchexcel]

Thanks for watching this video introduction to the VBA Editor for Excel. I hope you had fun learning how to make sheets visible, hidden and VERY hidden! You can get your free e-book and email course at the following link: DOWNLOAD EBOOK → launchexcel.lpages.co/first-macro-ebook
Please do leave comments below with any questions you have about learning Macros and VBA for Excel. And click on the LIKE button if you found this video helpful. Thanks for supporting my channel!

[@1gopalakrishnarao]

very informative. very eagerly waiting for other videos. Thanks a lot for having taken the trouble in doing this great job. On behalf of all my Excel lovers/addicts/users I sincerely thank you.

[@launchexcel]

Hi Gopala - it's my sincere desire to create more videos just like this for you!

[@dickyrock1]

Thanks Pal, great videos

[@lerouxstodart]

thanks for another interesting video

[@socatwin]

deffo very useful. looking forward to more videos from you. can you show in one of your future videos how to update an excel workbook without entering it? i.e. at work i have to drag down on several sheets one line in order to update the current date and figures which are linked to that. ideally i want to run something which does that for all the workbooks automatically. i presume i have to create something like a batch file but maybe there is another method ? cheers

[@victorchan7768]

Hi Philipp Saul. Thanks for commenting and I do have more videos planned :)
In terms of your current query if you want to read from and write to Excel workbooks without opening them at all, I think ADO is required - see support.microsoft.com/en-us/help/257819/how-to-use-ado-with-excel-data-from-visual-basic-or-vba and stackoverflow.com/questions/40766915/write-update-data-via-ado-to-closed-excel-workbook
Another suggestion is to use Access as the database and read/write using ADO, see www.vbaexpress.com/forum/showthread.php?22628-Solved-Add-data-to-a-closed-workbook
You can also open workbooks without making them visible to the user, see forums.devshed.com/visual-basic-programming-52/excel-vba-writing-data-closed-workbook-using-ado-329061.html

[@zackjohnson3352]

I know its been almost a year, but are we getting more of these videos at some point? They're are so helpful to someone trying to learn macros and VBA

[@launchexcel]

Hi Zack! Thanks for your comment. Sure I'm going to start making more videos about Macros and VBA. Let me know if there's any specific topic you want to learn about.

[@CROnishantarora]

Hi Victor,
Very helpfull videoes
you are doing gud.
which software are you using to split screen?

[@victorchan7768]

Hi CRO0320351 nishant arora! I split the screen using the standard Windows +left arrow / Windows +right arrow... in Windows 10 that automatically snaps the windows to left and right. There's a nice article on Windows10 Snap Assist here: www.cnet.com/how-to/how-to-use-windows-10s-new-snap-assist/
Also... I use Epic Pen for the onscreen writing. Very nice tool!'epic-pen.com/

[@meghraj5731]

Hi Victor, I wanted Excel to find Colored text and ask me what to replace it with? Can this be done using macros?

[@launchexcel]

Hi Meghraj, yes that can be done. There's a blog post by SumProduct (financial modelling group) that explains how to use Application.FindFormat to search for cell formatting, in combination with Range.Find.
You can read it here: www.sumproduct.com/blog/article/vba-blogs/vba-blog-find-the-right-look
Hope that helps to get you started.
- Victor
[Note: I'm not affiliated with SumProduct]

[@vivekava07]

Why do u need to hold Alt key while drawing the button? It works just fine without holding.

[@launchexcel]

Yes it does work fine without holding ALT. however if you hold down ALT you can snap the button edges to the grid lines... which can be useful if you like alignment to other parts of the spreadsheet. Also works for other shapes in Excel as well as charts... thanks for the question! - Victor