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

// FREE Excel E-book "Record Your First Macro"

// Recommended Excel Courses //

// Recommended Excel Templates //

If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.

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

- - - - - - - - - -
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]

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!

[asiasimpson]

If it wasn't for you, I don't know if I would have ever given macros a try. I wrote my first macro because of you and am now interested in learning more about macros & VBA, and eventually mastering it.

[gopalakrishnarao]

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.

[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

[UnfamiliarProxy]

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

[CROnishantarora]

Hi Victor,

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

[meghraj]

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

[vivekava]

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