Marco to hide/unhide worksheets

[KAH87]

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

 

[Joe4]

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]

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.

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