Hiding &Unhiding Sheets based on cell value.

[Zahid Marwat]

Peace be with you!
I have 4 sheets in a workbook, namely: TA&DA, SanctionOrder, Officers, Employees.
I need sheet TA&DA and SanctionOrder to be unhide constantly, while one out of the sheets Officers and Employees should unhide based on value in sheet TA&DA cell C6, which contains Servants' (Officers & Employees) grades starting from 1 to 22.
If cell C6 of sheet TA&DA is greater than 16, then sheet of Officers should unhide, otherwise sheet Employees should uunhide.
Is there any way out?
Thanks in anticipation for consideration.

Zahid

 

[Michael M]

try

Sub MM1()
If Sheets("TA&DA").Range("C6").Value > 16 Then
    Sheets("Employees").Visible = False
    Sheets("Officers").Visible = True
    Else: Sheets("Officers").Visible = False
        Sheets("Employees").Visible = True
End If
End Sub

 

[Zahid Marwat]

Thanks Sir. It had some problem with its first line i.e.

Sub MM1()

I changed the above first line with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Now it works well. Any suggestions regarding this; will it create problem in future, pls.

 

[Michael M]

No, it should be fine, as long as the code is in the TA&DA sheet module

 

[Zahid Marwat]

Thank you sir.
God bless you.

 

[Zahid Marwat]

Sir, there is an issue with the hiding & unhiding sheets. The value of cell C6 of TA&DA sheet is changed by formula (not manually) based on the servant name entered via userform.
If I click anywhere in TA&DA sheet, hiding & unhiding take place. Is there any way out of direct hiding & unhiding instead of clicking in the sheet.

 

[Michael M]

Change the first line from

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

to

Sub Worksheet_Calculate()

 

[Zahid Marwat]

Fantastic!
Sir, I need a message box on opening of the workbook to show value in cell P6 of Officer sheet or W20 of Establishment sheet (Both the cells have same value based on a formula.).
It is also for your kind information that a userform have already been set to auto open on opening of the workbook, code for is:

Private Sub Workbook_open ()
UserForm1.Show
End Sub

Any solution, please.