Macro-Hide & Unhide Sheets with condition

[Guest]

Dear All,

I would really appreciate all the available help.

Scenario:
I have 30 worksheets including a Database and a Helper worksheet.

The 28 worksheets are made up of Individual Worksheets of Products:

Sheet 1: XYZ Group
Sheet 2: ABC Group
Sheet 3: TVR Group
Sheet 4: CIA Group
till Sheet 28.

What I wanna do:
a) If cell A1 of ANY Sheet (30 sheets) = 1 or True Then Visible

b) If cell A1 of ANY Sheet (30 sheets) = 2 or False Then Unhide

Result of the Macro:
If 3 items are purchased, then 5 worksheets are displayed:

a) 3 product sheets are displayed; and

b) 2 (database and helper)

I have inserted Alpha Numberic data in cell A1 of Database and Helper
Worksheet, so they are not affected by this.


Thanks for the help.

 

[Guest]

Apology for a mistake:

What I wanna do:
a) If cell A1 of ANY Sheet (30 sheets) = 1 or True Then Visible

b) If cell A1 of ANY Sheet (30 sheets) = 2 or False Then HIDE!!

 

[Guest]

Hi,

Hiding/unhiding sheets is doable but I don't understand the circumstances in
which you want to do it:

a) If cell A1 of ANY Sheet (30 sheets) = 1 or True Then Visible
How does the 1 get into A1 if the sheet is (presumably) invisible?

b) If cell A1 of ANY Sheet (30 sheets) = 2 or False Then Unhide
Is this not the same as a)?

Mike

 

[Guest]

Dear Mike,
Yup!! A mistake there!! should have been Hide. Sorry!!

 

[Guest]

Hi,

Working backwards the will make any visible sheet hidden if a 2 is put in A1
of that sheet. Back to my original question. How is the 1 put in A1 to make
it visible again?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
For i = 1 To Worksheets.Count
If Worksheets(i).Visible = True Then
Visible = Visible + 1
End If
Next
If Target.Address = "$A$1" And Visible > 1 Then
If ThisWorkbook.Sheets.Count > 1 Then
If Target.Value = 2 Then ActiveSheet.Visible = False
End If
End If
End Sub


Mike

 

[Guest]

Oops

try this instead, left a line in that didn't work

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
For i = 1 To Worksheets.Count
If Worksheets(i).Visible = True Then
Visible = Visible + 1
End If
Next
If Target.Address = "$A$1" And Visible > 1 Then
If Target.Value = 2 Then ActiveSheet.Visible = False
End If
End Sub

 

[Guest]

Dear Mike,

Forgot to thank you for your respond. Really appreciate it.

Here Goes:
In cell A1 of all 28 sheets, I was thinking of using a Reference formula,
IndexMatch(ing) from the helper worksheet.

In Cell A1 of Sheet 1-28
a) =if(iserror(index(Helper!B1:B100,Sheet1!B1,Helper!A1:A100,))),2,1)
b) Sheet1 B1=is a Worksheet name formula

That way, I can centralise control over the sheets. There will be lots of
hiding & unhiding (kinda dynamic). lol.

Please help me to write a macro for this. (I have tried the macro recorder,
clueless)

Thanks.

 

[Guest]

I will try out the codes. Thanks.

 

[Guest]

Dear Mike,

The codes didn't respond unless I enter 1 or 2 manually in cell A1.

---------------------------------------------------------------------------
In cell A1 of all 28 sheets, I was thinking of using a Reference formula,
IndexMatch(ing) from the helper worksheet.

In Cell A1 of Sheet 1-28
a) =if(iserror(index(Helper!B1:B100,Sheet1!B1,Helper!A1:A100,))),2,1)
b) Sheet1 B1=is a Worksheet name formula

That way, I can centralise control over the sheets. There will be lots of
hiding & unhiding (kinda dynamic). lol.

Thanks.