1 sayfadan 1. sayfa

TreeView'de Hangi Hücrede Formül Olduğunu Gösterir

İletiTarih: 13 May 2011 23:24
Murat OSMA
Form üzerine;
1 Adet TreeView _
1 Adet CommandButton_
1 Adet Label ekleyin.
Kod: Tümünü seç
Private Sub UserForm_Initialize()
     With Me
          .CommandButton1.Caption = "Close"
          .Label1 = vbNullString
          .TreeView1.LineStyle = tvwRootLines
     End With
    Call TreeView_Populate
End Sub

Private Sub TreeView_Populate()
    Dim ws As Worksheet
     Dim rngFormula As Range
     Dim rngFormulas As Range
    With Me.TreeView1.Nodes
            .Clear
    For Each ws In ActiveWorkbook.Worksheets
               .Add Key:=ws.Name, Text:=ws.Name
               On Error Resume Next
               Set rngFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)
               On Error GoTo 0
               If Not rngFormulas Is Nothing Then
                    For Each rngFormula In rngFormulas
                         .Add relative:=ws.Name, _
                              relationship:=tvwChild, _
                              Key:=ws.Name & "," & rngFormula.Address, _
                              Text:="Range " & rngFormula.Address
                    Next rngFormula
               End If
               Set rngFormulas = Nothing
          Next ws
     End With
End Sub

Private Sub Treeview1_NodeClick(ByVal Node As MSComctlLib.Node)
     Me.Label1.Caption = Node.Key
End Sub

Private Sub CommandButton1_Click()
     Unload Me
End Sub