Sheets Hidden Visible if condition


    I Created 1 Work book with 5 Sheets aprox.

    1 cell named class & Command Button1 in Sheet1

If i click Command Button1 

I Want the Following

    if class < 3 , just Sheet2, sheet3, Sheet4 only shown, Sheet5 not shown.

    if class >= 3 all Sheets shown.

    How to write a macro?

I try the follwing macro, but not working

Sub Button1_Click()

If class <3 Then

  Sheets("Sheet5").Visible = False


Sheets("Sheet5").Visible = True

End If

Sheets("Sheet1").Visible = True

Sheets("Sheet2").Visible = True

Sheets("Sheet3").Visible = True

Sheets("Sheet4").Visible = True

End Sub

Answer Soon.



Selected Answer

Since you only wish to hide and show a single sheet your procedure needs only a single line of code.

Sheet5.Visible = IIf(Range("Class").Value < 3, xlSheetVeryHidden, xlSheetVisible)

First off, the worksheet's Visible property can't have the values True and False. Instead it accepts the values 0, 1 and 2 which are the values of the Exzcel built-in enumerations xlSheetVisible, xlSheetHidden and xlSheetVeryHidden. VBA's Iif function used in the above code works exactly like Excel's IF() worksheet function. Note that the named range "Class" must be scoped to be available in the whole workbook, not just the sheet it is on.

"Sheet5" in the above code is the sheet's CodeName. The CodeName is different from the tab's name which can be changed from the worksheet level. The CodeName remains the same whatever name you give to the tab. It can only be changed in the worksheet's properties in the VB Editor's Properties window. If you wish to address the same sheet by its tab name the code must look like this.

Worksheets("Sheet5").Visible = IIf(Range("Class").Value < 3, xlSheetVeryHidden, xlSheetVisible)

A sheet which is hidden can be made visible by the user by selecting it from the list of hidden sheets at Format > Hide & Unhide > Unhide sheets. A VeryHidden sheet isn't listed there. It can only be made visible by changing the Visible property in the VBE, either manually or using code like the above. Modify the code in this regard to suit your preference.

Finally, you might not need a button. Install the code below in the codesheet of the worksheet on which you have the "Class" range. It won't work if you install it anywhere else.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("Class").Address Then
        Sheet5.Visible = IIf(Target.Value < 3, xlSheetVeryHidden, xlSheetVisible)
    End If
End Sub

This code will show and hide Sheet5 as you enter a value in the "Class" cell.



   Super. Thanks.
And TWO More Same Doubts.
Doubt 1.
In the Same Condition,ie, class<3, 
The sheet name Sheet5 must be changed to some other name like XYZ, 
Otherwise ,ie, class >= 3 there is no change in sheet name.

Doubt 2.
In the same condition,ie class<3
In Sheet1,Sheet2,Sheet3,Sheet4,Sheet5 The Coloum B,C,D must be Hidden, all other Coloums Shown.
Otherwise,ie, class>=3, in all sheets ColoumB,C,D must be shown.

How to do this?
Please send Code like this.
Thanks in advance.
brsapril1978 (rep: 8) Apr 26, '18 at 11:19 am
Sorry brs, I don't do follow-ups. Your question was answered as asked. If you accept the answer I shall be glad to look at your next question (in a new thread).
Variatus (rep: 2454) Apr 26, '18 at 9:32 pm
Add to Discussion

Answer the Question

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