Could someone please help me with this code

Hutchington

New Member
Joined
Jan 23, 2014
Messages
4
I am hoping that someone can help me. I have the below code that loops through every worksheet in my workbook to find instances of names and makes all of these worksheets visible. In the Range "B2:B300" is a list of all the tabs worksheets within the workbook. Each tab can dynamically be made visible by inputting a "Y" in the cells "C2:C300", and likewise hidden by inputting an "N".

VBA Code:
Sub WS_Change()

Dim ws As Worksheet
Dim wkb As Workbook
Dim cel As Range
Dim cel2 As Range
Dim TN As Range
Dim TN2 As Range
Dim CDS As Object

Set wkb = ActiveWorkbook
Set ws = wkb.Worksheets(1)
Set CDS = Sheets("Control")

For Each cel In Range("F15:F100")
If UCase(cel.Value = "Y") Then
    For Each ws In Sheets 'This statement starts the loop
    For Each cel2 In ws.Range("A2:A100")
    If cel.Offset(0, -1).Value = cel2.Value Then
    ws.Visible = True
    Exit For
    End If
    Next cel2
    Next ws

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    For Each TN In CDS.Range("B2:B300")
        If ws.Name = TN.Value Then
        TN.Offset(0, 1) = "Y"
    End If
    Next TN
    End If
    Next ws
End If
Next cel
Call CDS.Activate
End Sub�

What I want to do is make the above dynamic too so I am not relying on a macro. I have converted the above into a Case Statement, but I cannot get it to work. The idea is, if you put a "Y" in a field within the range F15:F100 it will make visible all worksheets with that have the name in the offset cell. If you put an "N" in all the cells within the range C2:C300 with that dynamic code, thereby closing all of the tabs. It would then run the code in WS_Change() to see if there are any other cells with a "Y" in them and thereby reopen all the tabs with names still selected. That's the plan, but I can't seem to get it to work. Would someone mind having a look at the below and point out where I am going wrong, please.

VBA Code:
Sub WrkS_Change(ByVal Target As Range)

'Sub WS_Change()

Dim ws As Worksheet
Dim wkb As Workbook
Dim cel As Range
Dim cel2 As Range
Dim TN As Range
Dim TN2 As Range
Dim CDS As Object

Set wkb = ActiveWorkbook
Set ws = wkb.Worksheets(1)
Set CDS = Sheets("Control")

'On Error GoTo EH

If Application.Intersect(Target, CDS.Range(Cells(15, 6), Cells(100, 6))) Is Nothing Then Exit Sub
'Application.EnableEvents = False
Select Case UCase(Target.Value)
    Case "Y"
    For Each ws In Sheets
    For Each cel2 In ws.Range("A2:A100")
    If Cells(Target.Row, Target.Column - 1).Value = cel2.Value Then
    ws.Visible = True
    Exit For
    End If
    Next cel2
    Next ws

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    For Each TN In CDS.Range("B2:B300")
        If ws.Name = TN.Value Then
        TN.Offset(0, 1) = "Y"
    End If
    Next TN
    End If
    Next ws

    Case "N"

    For Each TN2 In CDS.Range("C2:C239")
    TN2 = "N"
    Next TN2

    For Each cel In Range("F15:F100")
    If UCase(cel.Value = "Y") Then
    For Each ws In Sheets 'This statement starts the loop
    For Each cel2 In ws.Range("A2:A100")
    If cel.Offset(0, -1).Value = cel2.Value Then
    ws.Visible = True
    Exit For
    End If
    Next cel2
    Next ws

    For Each ws In Worksheets
    If ws.Visible = xlSheetVisible Then
    For Each TN In CDS.Range("B2:B300")
        If ws.Name = TN.Value Then
        TN.Offset(0, 1) = "Y"
    End If
    Next TN
    End If
    Next ws
End If
Next cel

End Select

'EH:
'Application.EnableEvents = True
End Sub�
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,036
Office Version
  1. 2010
Platform
  1. Windows
I think you need to change this line:
VBA Code:
For Each cel2 In ws.Range("A2:A100")
to
VBA Code:
For Each cel2 In CDS.Range("A2:A100")
You need to do it in two places.
My only other comments is that the way you have coded this is over complicated and is very slow code because it doing multiple loops through multiple sheets every time you change anything on the worksheet. so I suspect you are going to notice that that worksheet ends up being frustratingly slow to use
It would be much much faster if you did it with variant arrays
 

Hutchington

New Member
Joined
Jan 23, 2014
Messages
4
I think you need to change this line:
VBA Code:
For Each cel2 In ws.Range("A2:A100")
to
VBA Code:
For Each cel2 In CDS.Range("A2:A100")
You need to do it in two places.
My only other comments is that the way you have coded this is over complicated and is very slow code because it doing multiple loops through multiple sheets every time you change anything on the worksheet. so I suspect you are going to notice that that worksheet ends up being frustratingly slow to use
It would be much much faster if you did it with variant arrays
Hi offthelip,
Thanks for the reply. I have checked the code with your change but it is still not working. The CDS refers to the control sheet. It is the only one that doesn't have the Names in that cell range. I would be very grateful if you could show me how I could set this up with variant arrays to speed the processing up.
Thanks
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,036
Office Version
  1. 2010
Platform
  1. Windows
I don't understand your second requirement at all, but the first one is very simple ( if I have understood it correctly) This code uses a variant array to load all the data in the control sheet, and it loops through them all and it change the visiblitly of the sheet only if the Y?N is different to the current visibility, so this will be very fast is suitable to put in the worksheet change event
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Control")
 inarr = .Range("B2:C300")
 For i = 1 To 299
  If inarr(i, 1) <> "" Then
    If inarr(i, 2) = "Y" And Not (Worksheets(inarr(i, 1)).Visible) Then
     Worksheets(inarr(i, 1)).Visible = True
    End If
    If inarr(i, 2) = "N" And (Worksheets(inarr(i, 1)).Visible) Then
     Worksheets(inarr(i, 1)).Visible = False
    End If
  End If
Next i
End With
End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,036
Office Version
  1. 2010
Platform
  1. Windows
I have been struggling to understand your requirements and I had a further thought and I was wondering whether you wanted to be able to control the visibility from any of the worksheets. So if you take the code I wrote in #4 above and put it in the "Control" worksheet code , put the list of the worksheet names in column B and Y/N in column C on the Control sheet.
and then put the following code in all the other worksheets. you will get the same layout with sheets names and Y/N on every worksheet and you can change it from any of the sheets. This works by copying the B2:C300 from the control sheet to the active sheet as you activate it and then copy the data back to the control sheet if you change it on the active sheet. when it is copied back the Control worksheet event is triggered which changes the visiblity. Try it out on a new worksheet with Sheet1 , Sheet2 , sheet3 and Control , to see how it works.
VBA Code:
Private Sub Worksheet_Activate()
With Worksheets("Control")
inarr = .Range("B2:C300")
End With
Range("B2:C300") = inarr

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C300")) Is Nothing Then
inarr = Range("C2:C300")
With Worksheets("Control")
  .Range("C2:C300") = inarr
End With
End If
End Sub
ABCYN.JPG
 

Forum statistics

Threads
1,144,340
Messages
5,723,804
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top