Watch Window: VBA to Retrieve Data & Selectively Delete Watches

[Devin]

Hey All,

I have recently started using the Watch Window a lot, and I as wondering if there is a way to retrieve data from the Watch Window. In the Object Browser, it looks like there are a few members of Watches (Add, Application, Count, Creator, Delete, Item, Parent). Is there a way to use "Item" to retrieve a watch? Or can I place the entire list on a regular sheet? Most of all, I'd like to be able to delete all watches belonging to a particular sheet.


Here is a sample of some of the code I have been using.

Sub MyWatches()
    If Application.Watches.Count = 0 Then GoTo NextStep
    
    QuestionYN = MsgBox("Do you want to clear the existing Watch List?", vbYesNo)
    If QuestionYN = vbYes Then
        Application.Watches.Delete
    End If
    
NextStep:
    Application.Watches.Add ActiveWorkbook.ActiveSheet.Range("A1")

    Application.CommandBars("Watch Window").Visible = True
    
End Sub

THANK YOU!!!

 

[RedBeard]

I assume you've looked here? https://msdn.microsoft.com/en-us/library/office/ff195056.aspx

 

[RickXL]

Hi,

This is one way you can delete all the Watches on a Sheet.

I don't know why I can't run a For Each on the Watches Collection. I have seen documentation to suggest you can but I can't seem to make it work. So I went for the "For i=Max to 0" approach, instead.

Sub WatchDeleteBySheet()
    Dim w  As Watches
    Dim i  As Long
    
    Set w = Application.Watches
    For i = w.Count - 1 To 0 Step -1
        If w(i).Source.Worksheet.Name = "Sheet2" Then w(i).Delete
    Next
End Sub

Note: You will probably need to change the Sheet Name from Sheet2!

I hope this helps,

 

[Devin]

RickXL,

Thank you again! Your insights were very help. I decided to use an existing UserForm I had populated with sheet names to add and remove watches.

As an interesting side note, the code wouldn't accept two Longs together (Dim i, j As Long). For some reason I had to separate them.
Here is a sample of how the code looks. It adds cells with yellow color.

Private Sub TWatch_Click()
    On Error Resume Next
    'Listbox code
    CA = 0
    For a = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(a) = True Then
    CA = CA + 1
    MySheet = ListBox1.List(a)
    End If
    Next a
    If (CA) = 0 Then GoTo EndNow
    
    Dim w  As Watches
    Dim i  As Long
    Dim j  As Long
    Dim x As Integer
    x = 0
    Set w = Application.Watches
    For i = w.Count - 1 To 0 Step -1
        If w(i).Source.Worksheet.Name = MySheet Then
        x = x + 1
        End If
        If x > 0 Then Exit For
    Next i
    
    Unload Me
    Worksheets(MySheet).Activate
    If x > 0 Then
    For j = w.Count - 1 To 0 Step -1
        If w(j).Source.Worksheet.Name = MySheet Then w(j).Delete
    Next j
    MsgBox MySheet & "'s Watch cells removed."
    Else
    
    MsgBox "Add Watches Code goes here."
    MyColor = 65535
    Dim R As Range
    Dim C As Integer
    Dim WS As String
    C = 0
    
    WS = ActiveSheet.Name
    
    Set R = Range("A1:Z100")
    For Each Cell In R
        If Cell.Interior.Color = MyColor Then
        If Application.WorksheetFunction.IsText(Cell) Then GoTo NextStep
        If IsError(Cell + 1) Then GoTo NextStep
        If Cell * 1 >= xValue Then
NextStep:
        Application.Watches.Add ActiveWorkbook.ActiveSheet.Range(Cell.Address)
        End If
        End If
    Next Cell
    End If
    Application.CommandBars("Watch Window").Visible = True
EndNow:
End Sub

 

[RickXL]

Thanks for the update. I will try it out in a few minutes.

As for Dim i, j As Long, VBA is a bit peculiar in this respect. What that means to VBA is:

Dim i As Variant, j As Long

If you leave something blank, it defaults to Variant.

Anyway, glad you got it sorted.

Regards,