PDA

View Full Version : Solved: Table of Contents



jmenche
09-29-2011, 07:44 AM
Folks,

I am trying to set up a simple TOC that sends the user to a particular cell in the next sheet (there are only two sheets). I am trying to use the Worksheet SelectionChange event so it fires right away BUT I am running into issues when I want to send the user to another sheet. I think it doesn't like the active sheet to lose focus.

The goal would be for someone to click on the cell address in column C and be magically transported to that cell in the next sheet ('Banner 1').

I attached a file but had to delete the actual data on the second sheet for file size purposes.

Any help is greatly appreciated.

:beerchug:

Paul_Hossler
09-29-2011, 08:15 AM
Maybe ...


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row > 10 And Target.Row < 188 And Target.Column = 3 Then
Call GoToBanner1(Target.Cells(1, 1))
End If
End Sub

Sub GoToBanner1(r As Range)
Dim ws As Worksheet

Set ws = Sheets("Banner 1")

ws.Select
ws.Range(r.Value).Select

End Sub




Paul

xld
09-29-2011, 08:16 AM
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row > 10 And Target.Row < 188 And Target.Column = 3 Then
Application.Goto Worksheets("Banner 1").Range(Target.Value)
End If

End Sub

jmenche
09-29-2011, 08:33 AM
Thanks fellas! They both work.