Before Double-Click
 

In my worksheet named "Index" - I have the names of all my worksheets (up to
date)
Some names include spaces. I'd like to be able to Double-Click on
a name (text) and have that worksheet become active. I've been toying
around now for an hour - in google, in help, close, but no cigar...
Can you assist?
TIA

Check out David McRitchie's BuildTOC macro:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm


Alternatively, select the cells with the names and choose
Insert/Hyperlink. Click the Document tab and in the Anchor textbox,
enter the sheetname!cellreference, with sheet names with spaces in
single quotes, e.g.:

Sheet2!A1
'Spaces in Title'!A1
NoSpacesNeeded!J10

Click OK

Note: Hyperlinks only take a single click to jump to their destination.

If you really want to double click, you'll need to use an event macro:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 1 Then
On Error Resume Next
Sheets(CStr(Target.Value)).Select
Cancel = True
On Error GoTo 0
End If
End Sub

Put that in your worksheet code module (right-click the worksheet tab
and choose View Code). Change the column number if you worksheet names
aren't in column A.



In article <t3Xwd.6677$jn.3778@lakeread06, "Jim May"
wrote:

In my worksheet named "Index" - I have the names of all my worksheets (up to
date)
Some names include spaces. I'd like to be able to Double-Click on
a name (text) and have that worksheet become active. I've been toying
around now for an hour - in google, in help, close, but no cigar...
Can you assist?
TIA

Thanks JE, the event-macro you offered did the trick.

"JE McGimpsey" wrote in message
...
Check out David McRitchie's BuildTOC macro:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm


Alternatively, select the cells with the names and choose
Insert/Hyperlink. Click the Document tab and in the Anchor textbox,
enter the sheetname!cellreference, with sheet names with spaces in
single quotes, e.g.:

Sheet2!A1
'Spaces in Title'!A1
NoSpacesNeeded!J10

Click OK

Note: Hyperlinks only take a single click to jump to their destination.

If you really want to double click, you'll need to use an event macro:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
If Target.Column = 1 Then
On Error Resume Next
Sheets(CStr(Target.Value)).Select
Cancel = True
On Error GoTo 0
End If
End Sub

Put that in your worksheet code module (right-click the worksheet tab
and choose View Code). Change the column number if you worksheet names
aren't in column A.



In article <t3Xwd.6677$jn.3778@lakeread06, "Jim May"
wrote:

In my worksheet named "Index" - I have the names of all my worksheets
(up to
date)
Some names include spaces. I'd like to be able to Double-Click on
a name (text) and have that worksheet become active. I've been toying
around now for an hour - in google, in help, close, but no cigar...
Can you assist?
TIA