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

%3CLINGO-SUB%20id%3D%22lingo-sub-2443977%22%20slang%3D%22en-US%22%3EHide%20a%20sheet%20based%20on%20a%20variable%20value%20in%20cell%20of%20other%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2443977%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20want%20to%20a%20hide%20a%20sheet%20based%20on%20Region%20value(For%20ex%3A%20If%20Region%20is%20other%20than%20North%20Europe%26nbsp%3B%20and%20South%20Europe%20in%20cell%20B5%20of%20Sheet1%2C%20I%20have%20to%20hide%20sheet2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Muneeswari_0-1623663675500.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F288430i79AFEA80AE295B1F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Muneeswari_0-1623663675500.png%22%20alt%3D%22Muneeswari_0-1623663675500.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20help%20me%20in%20this.%20Thanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EMuneeswari%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2443977%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2444051%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20a%20sheet%20based%20on%20a%20variable%20value%20in%20cell%20of%20other%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2444051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1078260%22%20target%3D%22_blank%22%3E%40Muneeswari%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab%20of%20Sheet1.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Not%20Intersect(Range(%22B5%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Select%20Case%20Range(%22B5%22).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20%22North%20Europe%22%2C%20%22South%20Europe%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Sheet2%22).Visible%20%3D%20xlSheetVisible%0A%20%20%20%20%20%20%20%20%20%20%20%20Case%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(%22Sheet2%22).Visible%20%3D%20xlSheetHidden%0A%20%20%20%20%20%20%20%20End%20Select%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(*.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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)

 

Muneeswari_0-1623663675500.png

Please help me in this. Thanks in advance

 

Thanks,

Muneeswari

2 Replies

@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.

Thanks @Hans Vogelaar for the reply,

Muneeswari_1-1623677556549.png    

Muneeswari_2-1623677736838.png

 

 

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).