Excel VB Script Issue - Hide/Show Sheets

Question

I found the below code which I have modified slightly for my needs. The issue I'm having is it doesn't do exactly what I'd like. Specifically, I have a drop down menu in A1 of each sheet with the names of the three sheets, Shipping, Orders, and Inventory in my workbook. What I'm trying to accomplish is whenever a user selects a drop down menu item regardless of the sheet they are working in, the relevant sheet is shown and the other two are hidden.

The below code works, but only if all three sheets have the same sheet name in the drop down selected, which becomes untenable when two sheets get hidden. I'm not exactly sure how to overcome this, but hopefully someone here who is much better at this than I am will have some advice.

Current VB Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Value = "Shipping" Then
        Sheets("Shipping").Visible = True
        Sheets("Orders").Visible = False
        Sheets("Inventory").Visible = False

    ElseIf Target.Value = "Orders" Then
        Sheets("Orders").Visible = True
        Sheets("Shipping").Visible = False
        Sheets("Inventory").Visible = False

    ElseIf Target.Value = "Inventory" Then
        Sheets("Inventory").Visible = True
        Sheets("Shipping").Visible = False
        Sheets("Orders").Visible = False

    End If
End Sub

Ross Brasseaux · Accepted Answer · 2013-10-22 23:22:25Z

1

Here is your code adapted for flexibility. This will hide any sheet that does not equal your target value, and unhide the sheet that DOES equal your target value.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim x As Worksheet
Set x = Excel.ActiveSheet

For Each ws In Excel.ActiveWorkbook.Worksheets
    If Trim(ws.Name) <> Trim(Target.Value) and ws.Name <> x.Name Then
        ws.Visible = xlSheetHidden
        Else
        ws.Visible = xlSheetVisible
    End If
Next ws

End Sub

If you are wondering about the Trim() command, it removes leading and trailing spaces from the string value. Those are sometimes hard to spot in sheet names :)

Edit

I added the ws.Name <> x.Name part of the if statement to make sure the current sheet (aka the sheet on which the drop-down control is located) remains visible.

edited Oct 22, 2013 at 23:22

answered Oct 22, 2013 at 23:08

Ross Brasseaux

4,1792 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2013-10-22 23:14:33Z

0

Start with all three sheets visible and use this code in all three sheets:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Shipping" Then
    Sheets("Shipping").Visible = True
    Sheets("Shipping").Select
    Sheets("Orders").Visible = False
    Sheets("Inventory").Visible = False

ElseIf Target.Value = "Orders" Then
    Sheets("Orders").Visible = True
    Sheets("Orders").Select
    Sheets("Shipping").Visible = False
    Sheets("Inventory").Visible = False

ElseIf Target.Value = "Inventory" Then
    Sheets("Inventory").Visible = True
    Sheets("Inventory").Select
    Sheets("Shipping").Visible = False
    Sheets("Orders").Visible = False

End If
End Sub

answered Oct 22, 2013 at 23:14

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Excel VB Script Issue - Hide/Show Sheets

2 Answers 2

Edit

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related