Bookmark Macro in Excel VBA

Question

I am fairly new to VBA so I was wondering if somebody can give a helping hand on something that I have been working on. It is a fairly simple concept and I believe that most of it is done apart from one functionality I can't seem to get to work.

So far, I have created a bookmark functionality in which the current selected cell or cells is highlighted and name the range as 'bookmark' the first time the VBA script is invoked. By using the VBA script the second time around the user is taken from the previously highlighted cell and the range name as well as the highlight is deleted. However, this functionality only works on one workbook and the corresponding sheets within it.

I would like to be able to use this functionality to all currently opened workbooks or perhaps all excel document within a specific folder. My code is as follows:

Sub setBookmark()

    Dim rRangeCheck As Range
    Dim myName As Name

    On Error Resume Next
    Set rRangeCheck = Range("bookmark")
    On Error GoTo 0
    If rRangeCheck Is Nothing Then

        ThisWorkbook.Names.Add "bookmark", Selection
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Else
        Application.Goto Range("bookmark")

        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        For Each myName In ThisWorkbook.Names
            If myName.NameLocal = "bookmark" Then myName.Delete
        Next
    End If

End Sub

user3514930 user3514930 · Accepted Answer · 2014-05-06T09:37:59

Modify like that:

Dim rRangeCheck
Dim myName As Name

On Error Resume Next
rRangeCheck = ""
rRangeCheck = Application.Names("bookmark")
On Error GoTo 0
If rRangeCheck = "" Then

    ActiveWorkbook.Names.Add "bookmark", Selection
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Else
    Application.Goto Range("bookmark")
    Application.Goto Range("bookmark")

    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    For Each myName In ActiveWorkbook.Names
        If myName.NameLocal = "bookmark" Then myName.Delete
    Next
End If

The double Applicatio.Goto it's to have the selection of Workbook + the Sheets and the cell...
If you remove, only the workbook it's selected.

Bookmark Macro in Excel VBA

1 Answers