Forum Discussion

Muneeswari's avatar
Muneeswari
Copper Contributor
Jun 14, 2021

Hide a sheet based on a variable value in cell of other Sheet

I want to a hide a sheet based on Region value(For ex: If Region is other than North Europe  and South Europe in cell B5 of Sheet1, I have to hide sheet2)   Please help me in this. Thanks in ...
BI & Data Analysis
Macros and VBA
HansVogelaar's avatar
HansVogelaar
MVP
Jun 14, 2021

Muneeswari 

Right-click the sheet tab of Sheet1.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B5"), Target) Is Nothing Then
        Select Case Range("B5").Value
            Case "North Europe", "South Europe"
                Worksheets("Sheet2").Visible = xlSheetVisible
            Case Else
                Worksheets("Sheet2").Visible = xlSheetHidden
        End Select
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

  • Muneeswari's avatar
    Muneeswari
    Copper Contributor
    Jun 14, 2021

    Thanks HansVogelaar for the reply,

        

     

     

    All these hiding and all I want to do in NPrinting excel. The Region Variable I am taking from  QlikSense.

     

    I tried with the VBA code that have been provided by you, but it is not working for me. (I have used the macro-enabled workbook (*.xlsm) template  for my NPrinting excel).

     

     

Resources