CODEWITHSUNDEEP
excelvba
Cthulhu
Cthulhu

Reputation: 103

What is this macro and how has it been created?

I have been sent an Excel Macro-Enabled Worksheet, which contains over 50 sheets. There is a contents sheet at the beginning for easier navigation around the document; there is a cell for each sheet which you can click on and be re-directed to the relevant place.

However, there are not buttons in these cells. There doesn't appear to be anything in the cell except text, where the name of the relating sheet is written. Not only will left-clicking on the cell take you to the relevant sheet, but also right-clicking and by navigating to the cell using the arrow keys on the keyboard. I've never come across anything like this before, where buttons haven't been used.

The document is protected and so I cannot delve into how the creator has managed to do this. I need to recreate this document but am completely unsure of what they have done and so cannot replicate it. I'm assuming this is some sort of macro, though there is nothing listed in the 'Macros' tool on the Developer toolbar.

The document contains sensitive information and so I cannot include a screenshot.

Upvotes: 0

Views: 47

Answers (1)

MikeD
MikeD

Reputation: 8941

You should be able to find out easily if the jump table in Sheet1 are hyperlinks (check the cursor behaviour). If not then I recon this behaviour is facilitated by a Worksheet_SelectionChange() trigger.

Suppose all your sheet names are listed in Sheet1 in a range called [JumpTable], then this code in Sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("JumpTable")) Is Nothing Then
        If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
            Sheets(Target.Value).Activate
        End If
    End If
End Sub

will get you to the sheet whose name you click on Sheet1.

The rest is up to your imagination!

Upvotes: 1

Related Questions