Multiple VBA Codes on 1 sheet

[newbie297]

I'm a complete novice with VBA codes, I've added the below to hide columns based on a drop down menu but I also want to hide tabs based on the same drop down menu but have no idea how, any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 3 And Target.Value = "No" Then
Application.Columns("F").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("F").Select
Application.Selection.EntireColumn.Hidden = False
End If

If Target.Column = 2 And Target.Row = 3 And Target.Value = "Yes" Then
Application.Columns("E").Select
Application.Selection.EntireColumn.Hidden = True
Else
Application.Columns("E").Select
Application.Selection.EntireColumn.Hidden = False
End If
End Sub

 

[bs0d]

To hide a sheet, use:

ActiveWorkbook.Sheets("your-sheet-name").Visible = xlSheetHidden

You also may also chose xlSheetVeryHidden, which would prevent the sheet from being unhidden through the Excel UI.

 

[louisH]

Hi,

To hide a sheet :

Worksheets("SheetName").Visible = xlSheetHidden

To make it visible again :

Worksheets("SheetName").Visible = xlSheetVisible

 

[Fluff]

Hi & welcome to MrExcel.

Your code can be condensed to this

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target = Range("B3") Then Exit Sub
   Columns("F").Hidden = Target.Value = "No"
   Columns("E").Hidden = Target.Value = "Yes"
   
End Sub

Which sheets do you want to hide/unhide?

 

[newbie297]

When the drop down answer is Yes it hides column E and I also want it to hide sheet 5 which is called "No Multibuy Packing Note"
And when the drop down answer is No It hides column F and I also want it to hide sheet 4 which is called "Multibuy Packing Note"
The code is all on sheet 3

I hope that makes sense.

 

[Fluff]

Try
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Target = Range("B3") Then Exit Sub
Columns("F").Hidden = Target.Value = "No"
Columns("E").Hidden = Target.Value = "Yes"
Sheets("No Multibuy Packing Note").Visible = Not Target.Value = "Yes"
Sheets("Multibuy Packing Note").Visible = Not Target.Value = "No"
End Sub

 

[newbie297]

That works perfectly, thank you so much for your help

 

[Fluff]

Glad to help & thanks for the feedback