Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

hide & unhide sheet based on dropdown

0

hello

I  just try  showing   sheet based on selection from dropdown  and  hide  the  others.

every  time  when  select  sheet  should  show based on A1   and  hide  the  others , but  gives mismatch error in this  line  

 MyChoice = Target.Value
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyChoice As Long
Dim sh As Worksheet
Application.ScreenUpdating = False
If Not Intersect(Target, [A1]) Is Nothing Then
    MyChoice = Target.Value
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> "Input" Then sh.Visible = False
    Next sh
    Sheets(MyChoice).Visible = True
End If
Application.ScreenUpdating = True
End Sub

any  idea  to  fix error ?

Answer
Discuss

Answers

0
Selected Answer

Hi Alaa

I think you just need to declare the variable MyChoice as a String (which a sheet name is), not Long (a number).  That will work if cell A1 contains a valid worksheet name (e.g. Sheet2).

The code below should work even if the entry in A1 is not a valid worksheet name (with the file you attached- where the sheet name is "input" not "Input"):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyChoice As String
Dim sh As Worksheet

Application.ScreenUpdating = False
If Intersect(Target, [A1]) Is Nothing Then Exit Sub

MyChoice = Target.Value

For Each sh In ThisWorkbook.Worksheets
    Select Case sh.Name
        Case MyChoice
            ' reveal MyChoice (if sheet exists)
            sh.Visible = True
        Case Is <> "input"
        ' otherwise hide any sheet which exists (apart from Input)
            If sh.Name <> MyChoice Then sh.Visible = False
    End Select
Next sh

Application.ScreenUpdating = True
End Sub

See attached file.

Hope this works for you.

Discuss

Discussion

Hi John,
it  changes  to  another error  method visible of object_worksheet failed in this line
sh.Visible = False
Alaa (rep: 12) Apr 14, '22 at 6:32 am
actually  doesn't  work for  me 
you  can  see  the  file 
Alaa (rep: 12) Apr 14, '22 at 7:51 am
See revised answer please (just got to my PC)
John_Ru (rep: 3222) Apr 14, '22 at 8:00 am
Revised again (since your file have a sheet name "input", which dosen't match your original code).

If only one sheet is shown on your file now, right click, choose Unhide and show "input" again
John_Ru (rep: 3222) Apr 14, '22 at 8:05 am
Just added a file for you
John_Ru (rep: 3222) Apr 14, '22 at 8:09 am
very  well ! 
many  thanks .
Alaa (rep: 12) Apr 14, '22 at 8:15 am
Great! Thanks for selecting my Answer (I tweaked it slightly, including correcting a typo in the code comments)
John_Ru (rep: 3222) Apr 14, '22 at 9:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login