Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
Dim r As Long, aloc(), rng As Range
Me.Columns("A:A").EntireRow.Hidden = False
r = Me.Cells(Me.Rows.count, "A").End(xlUp).Row
If r < 3 Then Exit Sub
On Error GoTo End_
Speed_up True
Set rng = Me.Range("A3:A" & r)
rng.EntireRow.Hidden = True
aloc = Me.Range("A1").Resize(r, 2)
Set rng = Hide_Row(aloc, Target.Value)
If Not rng Is Nothing Then rng.EntireRow.Hidden = False
Dim book As Workbook, count As Long
Set book = ThisWorkbook
count = book.Worksheets.count
AnSheet book, count
HienSheet book, aloc, Target.Value, count
End_:
Speed_up False
End Sub
Private Function Hide_Row(ByRef aloc(), ByVal Target As String) As Range
Dim i As Long, k As Long, rng As Range
For i = 3 To UBound(aloc, 1)
If CStr(aloc(i, 1)) = Target Then
Set rng = Cells(i, 1)
If Not Hide_Row Is Nothing Then
Set Hide_Row = Union(Hide_Row, rng)
Else
Set Hide_Row = Cells(i, 1)
End If
End If
Next i
End Function
Private Sub AnSheet(book As Workbook, ByVal count As Long)
Dim i As Long, shName As String, asheet()
If count = 1 Then Exit Sub
If Not Me.Visible = xlSheetVisible Then _
Me.Visible = xlSheetVisible
For i = 1 To count
shName = Sheets(i).Name
If SheetExists(book, shName) Then
If shName <> Me.Name Then
Sheets(shName).Visible = xlSheetHidden
End If
End If
Next i
End Sub
Private Sub HienSheet(book As Workbook, ByRef aloc(), ByVal Target As String, ByVal count As Long)
Dim i As Long, r As Long, asheet(), shName As String, sName As String
ReDim asheet(1 To count)
For r = 1 To UBound(aloc, 1)
If CStr(aloc(r, 1)) = Target Then
sName = aloc(r, 2)
For i = 1 To count
shName = Sheets(i).Name
If SheetExists(book, shName) Then
If shName <> sName Then
Sheets(shName).Visible = xlSheetVisible
End If
End If
Next i
End If
Next r
End Sub
Public Function SheetExists(book As Workbook, sheetname As String) As Boolean
Dim sht As Worksheet
SheetExists = False
For Each sht In book.Worksheets
If sheetname = sht.Name Then
SheetExists = True
Exit Function
End If
Next
End Function
Public Function check_hide_sheet(book As Workbook, sheetname As String) As Boolean
Dim sht As Worksheet
Set sht = book.Worksheets(sheetname)
If Not sht.Visible = xlSheetVisible Then
check_hide_sheet = True
Else
check_hide_sheet = False
End If
End Function
Public Function Speed_up(bln As Boolean)
With Application
.ScreenUpdating = Not (bln)
.DisplayAlerts = Not (bln)
.Calculation = IIf(bln, xlCalculationManual, xlCalculationAutomatic)
End With
End Function
