Hello Excellers, today let’s look at a really quick way to hide an Excel worksheet if a specific word appears in another worksheet.  I find this really useful when I am working on an Excel solution, and I have not as yet released the worksheet for general use.  The method uses a quick Excel VBA macro.

So, if the spreadsheet is still in draft mode or development, then I enter the work DRAFT in cell A1 of Sheet 1.  This triggers the ‘work in progress’ worksheet to be hidden until I want it to be seen by users.  This really works for me, so I thought I would share it with my subscribers.  Just follow the steps below.

Let’s Create The Macro.

We want to the work in progress worksheet to be hidden if we enter the word DRAFT into cell A1 of Sheet 1.  For this reason, I will use the following event

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

Now with VBA you may be used to creating a new module to stores the code.  But for this code to work we need to have it sit on the sheet where the cell value change will be taking place.  That happens to be Sheet 1 with cell reference of A1 where the cell content changes will happen.  So we need to get into the VBA environment to get this code written.

• Open the Visual Basic Editor by hitting ALT+F11
• Double click Sheet 1 under the  Microsoft Excel Objects on the left-hand side.  This will then open a window on the right-hand side.
• We now can write our macro

I have kept this such a simple macro, it is all we need.  The logic behind this is as below.

Excel Macro Logic

If cell A1 in Sheet1 contains DRAFT then hide the Sheet named Solution

If cell A1 in Sheet1 is blank then unhide the Sheet named Solution

So to enter this, after we have double clicked in Sheet1, use the Object Drop down box and select Worksheet

Then we need to select the procedure of Selection Change

 

This automatically gives us the Private Sub Worksheet_SelectionChange(ByVal Target As Range) event details, and whenever we change the selection, the event is triggered.  This is exactly what we want for our solution today.

So just enter the code which states, if A1 in Sheet1 contains the text string DRAFT then set the Visible property of the worksheet name Solution to hidden, and if the cell A1 from Sheet1 is blank then set the Visible property to Visible.  We could make the worksheet very hidden which means that it does not appear even when the user right clicks on the sheet tabs to see if there any hidden worksheets in the workbook.  In this case, we would change the Visible property to xlSheetVeryHidden.

To see this in action, this is the sheet tabs visibility with the property of the sheet Solution set to xlSheetHidden.  (you can still see it using the unhide commands).

 

Changing it to xlSheetVeryHidden, we cant see it unless we go to the Developer Tab to unhide it.

That’s it, folks.  I hope you enjoyed this instalment of Macro Monday.

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Mondays series. Click on the link below

 

How To Excel At Excel – Macro Mondays Blog Posts.