Marco to hide/unhide worksheets

KAH87

New Member
Joined
Feb 8, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone, I'm new to macros and need some help please!

I'm trying to hide/unhide worksheets based on a cell value.

For example I have entered the below which successfully works:
Private Sub Worksheet(VAT)
If [I29] = "Yes" Then
Sheets("VAT Reg").Visible = True
Else
Sheets("VAT Reg").Visible = False
End If
End Sub

However, I need to add two more:
1) If cell T16 = "Yes" hide "Company_Reg"
If not "Company_Reg" is visible
2) If cell I43 = "Yes" visible "Bank Details"
If cell I43 - "No" hide "Bank Details"

I've tried adapting the above code (which I found whilst endlessly googling!) but with no success!

Any help would be much appreciated :)
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,015
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

How exactly are you calling/running this code?
Why are you passing it a variables named "VAT"?
You do not appear to be using it anywhere in your code.

Note that to add other conditions, simply add two more IF/THEN/ELSE blocks (like you have now) below the first one in your current procedure.

Also, you should NEVER use reserved words like "Worksheet" for the names of your procedures, functions, and variables. Doing so can can errors and unexpected results.
Reserved words are words of existing objects (like "Worksheet"), functions, properties, etc.
 

edmitchell

Board Regular
Joined
Dec 18, 2017
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hi, based on your description, I would suggest the following code will do what you want/need.

As you will see, it checks to see if the tab which contains the cells that have just been updated/changed is in the tab called "Sheet1", if this is not the tab containing I29, etc then you just need to update the VBA to reflect the name of your tab/sheet.

As per your description the relevant cells are checked for "Yes" (capital "Y") so again you might need to change if you want the tab's hidden irrespective of the case of "Yes" (e.g. if the user enters "yes", or "YES").

Hope it helps.

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'Only execute if on Initial tab (e.g. "Sheet1")
If (Sh.Name <> "Sheet1") Then
    Exit Sub
End If

'if cell changed is not one for us, skip settings
If (Target.Address = "$I$29" Or Target.Address = "$T$16" Or Target.Address = "$I$43") Then
    
    'Cell with VAT Reg value
    If [I29] = "Yes" Then
        Sheets("VAT Reg").Visible = True
    Else
        Sheets("VAT Reg").Visible = False
    End If
    
    'Cell with Company Reg value
    If [T16] = "Yes" Then
        Sheets("Company_Reg").Visible = True
    Else
        Sheets("Company_reg").Visible = False
    End If
    
    'Cell with Bank details value
    If [I43] = "Yes" Then
        Sheets("Bank Details").Visible = True
    Else
        Sheets("Bank Details").Visible = False
    End If
    
End If
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,118
Messages
5,640,216
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top