Name Lister Phần II
Đây là đọan mã của UserForm1
[/IMG]
Option Explicit
Dim WarningWasShown As Boolean
Dim Output As Worksheet
Dim EventsEnabled As Boolean
Private Sub UserForm_Activate()
Dim i As Long
DoEvents
GetAllNames
EventsEnabled = True
obAll_Click
LabelRefersTo.Font.Bold = False
End Sub
Private Sub UserForm_Initialize()
Dim sht As Object
EventsEnabled = False
Me.Caption = APPNAME
Label1.Caption = "Names in " & ActiveWorkbook.Name
For Each sht In ActiveWorkbook.Sheets
ComboSheetList.AddItem sht.Name
Next sht
With ComboSheetList
.AddItem "(no sheet)"
.Value = ActiveSheet.Name
.Enabled = False
End With
With LabelRefersTo
.Caption = "Retrieving names..."
.Font.Bold = True
End With
DoEvents
End Sub
Private Sub ListBox1_Click()
Dim i As Long
If Not EventsEnabled Then Exit Sub
On Error Resume Next
For i = 1 To NameCount
If AllNames(i).TheName = ListBox1.Value Then
With LabelRefersTo
.Caption = AllNames(i).RefersTo
.ControlTipText = .Caption
End With
ListBox1.ControlTipText = ListBox1.Value
If AllNames(i).Hidden Then UnhideButton.Enabled = True Else UnhideButton.Enabled = False
If AllNames(i).Bad Then GotoButton.Enabled = False Else GotoButton.Enabled = True
Exit For
End If
Next i
If Err <> 0 Then
With LabelRefersTo
.Caption = ""
.ControlTipText = .Caption
End With
End If
If ListBox1.Value = "(none)" Then
GotoButton.Enabled = False
DeleteButton.Enabled = False
DeleteAll.Enabled = False
Else
GotoButton.Enabled = True
If Not ActiveWorkbook.ProtectStructure Then
DeleteButton.Enabled = True
DeleteAll.Enabled = True
End If
End If
On Error GoTo 0
End Sub
Private Sub obAll_Click()
' Show all names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub
Private Sub obWorkbook_Click()
' Show workbook level names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
If Not AllNames(i).SheetLevel Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub
Private Sub obSheetLevel_Click()
' Show sheet level names
Dim i As Long
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
ListBox1.Clear
DoEvents
For i = 1 To NameCount
If AllNames(i).SheetLevel Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub
Private Sub ComboSheetList_Change()
Dim i As Long
Dim ShtName As String
If Not EventsEnabled Then Exit Sub
ListBox1.Clear
DoEvents
ShtName = ComboSheetList.Value
ComboSheetList.ControlTipText = ComboSheetList.Value
If ShtName = "(no sheet)" Then ShtName = "(none)"
For i = 1 To NameCount
If AllNames(i).BySheet = ShtName Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub
Private Sub obBySheet_Click()
Call ComboSheetList_Change
With ComboSheetList
.Enabled = True
.SelStart = 0
.SelLength = 100
.SetFocus
End With
End Sub
Private Sub obHidden_Click()
' Show hidden names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = True
For i = 1 To NameCount
If AllNames(i).Hidden Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then
ListBox1.AddItem "(none)"
UnhideButton.Enabled = False
End If
ListBox1.ListIndex = 0
End Sub
Private Sub obLinked_Click()
' Show linked names
Dim i As Long
ListBox1.Clear
DoEvents
ComboSheetList.Enabled = False
UnhideButton.Enabled = False
For i = 1 To NameCount
If AllNames(i).Linked Then ListBox1.AddItem AllNames(i).TheName
Next i
If ListBox1.ListCount = 0 Then ListBox1.AddItem "(none)"
ListBox1.ListIndex = 0
End Sub
Chúc các bạn thành công
Lê Văn Duyệt
