Show/Hide Several Sheets based on Data Validation

[SwampManJim]

Greetings, new user who is not a VBA pro and would appreciate help.

I'm also working on a navigate sheets ribbon, which I can post/reciprocate when I finish.

I'm working on a workbook, where the user will select from a list (datavalidation) with a range name "AssetType", and based on their choice sheets will unhide.

I've got maybe 10 items in the datavalidation and have 10 columns with the sheet names I want per choice. I can name these ranges.

I need help after the "then", to loop through my asset types.

For "Vehicle" show the sheets named in range "VehicleSheets"
For "Hand Tool" show the sheets named in Range "HandTools"
etc....

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("AssetType")) Is Nothing Then
If Range("AssetType").Value = "Vehicle" And Not IsEmpty(Range("AssetType")) Then


Me.Visible = xlSheetHidden
Else
Me.Visible = xlSheetVisible


End If


End If
End Sub

 

[AKR22]

I dont know where this code is running from, however I recommend that you run this code in the data validation drop box macro. This way you dont have to loop through each option, instead you can create case logic for each value selected.

For showing and hiding the sheet:

[COLOR=#303336][FONT=inherit]ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Name"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Visible [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlSheetVeryHidden or xlSheerHidden or xlSheetVisible[/FONT][/COLOR]

Note: "xlSheetVeryHidden" will make the sheet hidden and only visible again through vba code.

Cheers,

AKR22

 

[SwampManJim]

I dont know where this code is running from, however I recommend that you run this code in the data validation drop box macro. This way you dont have to loop through each option, instead you can create case logic for each value selected.

For showing and hiding the sheet:

[COLOR=#303336][FONT=inherit]ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Name"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Visible [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlSheetVeryHidden or xlSheerHidden or xlSheetVisible[/FONT][/COLOR]

Note: "xlSheetVeryHidden" will make the sheet hidden and only visible again through vba code.

Cheers,

AKR22

So Is "Name" where I put my range name (MachinerySheets which refers to a list of sheet names in B2:B10)?

Sorry for the newb questions, I'm trying to pick this up as fast as possible

 

[SwampManJim]

So this is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("AssetType")) Is Nothing Then
        Select Case Range("AssetType")
            Case "Vehicle": Macro1
            Case "HandTools": Macro2
            Case "Machinery": Macro3
            Case "LeasedEquipment": Macro4
                  End Select
    End If
    End Sub

Now I need the code for each "Macro1,2,3,4" to unhide a list of sheets that are in a named range

 

[AKR22]

No, your sheet name goes there. No range needed either, you are making a change to the sheet. If you're working in the same worksheet you can just say:

[COLOR=#303336][I]Active[/I][/COLOR][COLOR=#303336][I]Sheet[/I][/COLOR][COLOR=#303336][I].[/I][/COLOR][COLOR=#303336][I]Visible [/I][/COLOR][COLOR=#303336][I]=[/I][/COLOR][COLOR=#303336][I] xlSheetVeryHidden or xlSheetHidden or xlSheetVisible[/I][/COLOR]

Otherwise:

[COLOR=#303336][I]ActiveWorkbook.Work[/I][/COLOR][COLOR=#303336][I]Sheets("sheetnamehere")[/I][/COLOR][COLOR=#303336][I].[/I][/COLOR][COLOR=#303336][I]Visible [/I][/COLOR][COLOR=#303336][I]=[/I][/COLOR][COLOR=#303336][I] xlSheetVeryHidden or xlSheetHidden or xlSheetVisible[/I][/COLOR]

AKR22

 

[AKR22]

All you need now is to put this into each case:

[FONT=Verdana]For Each cell In Range(nameofrangehere)

[/FONT][FONT=Verdana]     Worksheets(cell).Visible = xlSheetHidden

[/FONT]
[FONT=Verdana]Next cell[/FONT]

Be sure to write the declare the named range at the beginning of the sub.

AKR22