Hide or Unhide a Tab in a Workbook, if one value of a Column is true

[Krist Cheung]

Dear all EXCEL experts:

How are you?

I have a column in a excel workbook, which have drop down list in every single cell in this column.

There are five selection, A / B / C / D / E.

There is an extra tab needs to be filled in, if the selection (in any of the cells in this column) is "C".

I managed to get it work on the first cell of the column, but when I copied and pasted, expand the code applies to each cell of the column, it does not work.

Could you please advise if there is any way to do it?

Please see below as the Code that I wrote:

Private Sub Worksheet_Change(ByVal Target As Range)

If [C4] = "C" Then
Sheets("Cells").Visible = True
Else
Sheets("Cells").Visible = False
End If

End Sub

Your urgent assistance is much appreciated

 

[DanteAmor]

Hi and welcome to MrExcel and happy new year

I have a column in a excel workbook, which have drop down list in every single cell in this column.

Which column?
I'm going to assume column M. (For example purposes)

Check if the following code is close to what you need. Otherwise you will have to explain step by step what you want to do with each cell and with each letter written in the column, starting with what is that "column"?

Private Sub Worksheet_Change(ByVal Target As Range)
  
  'When you modify any cell in column M
  If Not Intersect(Target, Range("M:M")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Select Case Target.Value
      Case "C"
        Sheets("Cells").Visible = True
      Case "D"
        'Instructions if you write the letter D
      Case "E"
        'Instructions if you write the letter E
        
        
      Case Else
        Sheets("Cells").Visible = -1
      
    End Select
    
  End If
End Sub

I hope to hear from you very soon
Sincerely
Dante Amor

 

[Krist Cheung]

Dear Dante:

Thanks for your reply.

Sorry if my previous message does not explain clearly.

so in Column C, I have a drop down menu listed below:

so if the user make a selection from the drop down menu to "Pharma & Healthcare, Perishable".

Then, the Tab "Pharma and Perishable" will be appeared for them to fill in.

If the other selection is made, then the above mentioned tab will be hidden.

Also, it should apply to the whole Column C, if one of the cells in Column C have this selection, it should be appeared.

I hope this can make it clear.

Krist

 

[DanteAmor]

so if the user make a selection from the drop down

I'm going to assume that you are always going to select an item from a validation list.
I'm also going to assume that the validation list is somewhere on the same sheet.


If the above is correct, then put the following code in the events of the sheet where you want this to work.
Note Sheet Event:

Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.​

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range, c As Range
  Dim sName As String
   
  Application.ScreenUpdating = False

  'When you modify any cell in column C
  If Not Intersect(Target, Range("C4:C" & Rows.Count)) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
   
    If Not Intersect(Target, ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
      Set r = Range(Target.Validation.Formula1)
      For Each c In r
        sName = c.Value
        If Evaluate("ISREF('" & sName & "'!A1)") Then
          If sName <> Target.Value Then
            Sheets(sName).Visible = xlSheetHidden
          End If
        End If
      Next
      sName = Target.Value
      If Evaluate("ISREF('" & sName & "'!A1)") Then
        Sheets(sName).Visible = xlSheetVisible
        Sheets(sName).Activate
      End If
    End If
  End If
End Sub

So, if you select a data item in column C, the sheets are automatically hidden and only the sheet with the name of the selected item is shown and the sheet is also activated.
If you don't want the sheet to be activated, then delete this line from the macro: Sheets(sName).Activate

Try it and tell me the results.
Cordially
Dante Amor