Clicking cells - run VBA
 

I have a Workbook full of worksheets with bank account details - the first
sheet is an Index sheet with a summary of the various accounts worksheets -
I want to select the appropriate worksheet and display it when I click (or
maybe double click) the account name on the Index sheet - can I do that ?

Thanks

Clicking cells - run VBA
 

Right click on the Index Sheet tab (i.e., on the sheet's name (e.g.
Sheet1) and select ViewCode. Then VBE will open.
put this code in the
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
On Error GoTo NoSuchSheet:
ThisWorkbook.Worksheets(CStr(Target.Value)).Activa te
ExitSub:
On Error GoTo 0
Exit Sub
NoSuchSheet:
MsgBox "There is no such Sheet in this file!"
Resume ExitSub:
End Sub



On Jan 26, 2:00*pm, Isis wrote:
I have a Workbook full of worksheets with bank account details - the first
sheet is an Index sheet with a summary of the various accounts worksheets -
I want to select the appropriate worksheet and display it when I click (or
maybe double click) the account name on the Index sheet - can I do that ?

Thanks

Clicking cells - run VBA
 

AB wrote in
:

Right click on the Index Sheet tab (i.e., on the sheet's name (e.g.
Sheet1) and select ViewCode. Then VBE will open.
put this code in the
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
On Error GoTo NoSuchSheet:
ThisWorkbook.Worksheets(CStr(Target.Value)).Activa te
ExitSub:
On Error GoTo 0
Exit Sub
NoSuchSheet:
MsgBox "There is no such Sheet in this file!"
Resume ExitSub:
End Sub



On Jan 26, 2:00*pm, Isis wrote:
I have a Workbook full of worksheets with bank account details - the
firs
t
sheet is an Index sheet with a summary of the various accounts
worksheets
-
I want to select the appropriate worksheet and display it when I
click (o
r
maybe double click) the account name on the Index sheet - can I do
that ?

Thanks



Thanks AB, that was a help.

If I wanted to link to sheets in other workbooks is there an adaptation I
can make ?

Thanks again

Clicking cells - run VBA
 

Try using hyperlinks. No VBA needed.

Best wishes Harald

"Isis" wrote in message
...
I have a Workbook full of worksheets with bank account details - the first
sheet is an Index sheet with a summary of the various accounts
worksheets -
I want to select the appropriate worksheet and display it when I click (or
maybe double click) the account name on the Index sheet - can I do that ?

Thanks

Clicking cells - run VBA
 

On 26 Jan 2011 14:00:47 GMT, Isis wrote:

I have a Workbook full of worksheets with bank account details - the first
sheet is an Index sheet with a summary of the various accounts worksheets -
I want to select the appropriate worksheet and display it when I click (or
maybe double click) the account name on the Index sheet - can I do that ?

Thanks

My no code required solution is to simply use hyperlinks...

You can make "hyperlinks" and then point at "a location in this file"
and select the sheet. That hyperlink can be copied down the page,
replacing your index entries. You must then edit the other hyperlinks to
match what they should point to.

Another way, my personal favorite... is to go to a specific cell in
each sheet and give that cell a name (range name) That name could be the
account name or other cryptic naming schema you decide upon.

Then, when you make the hyperlinks, you simply point at the range name
for each sheet, and it will "pop" you right there instantly.

I have a 52 week example on the Microsoft user submitted template
website. There are "Jump Links" that take the user to any of the 52
worksheets, OR any of the 52 Data Charts for those worksheets.

Of course, the tabs are available as well, but the links all have
custom wording, and even have custom "comments" that pop up when you
hover over the link describing what it will do or where it will go.


http://tinyurl.com/27zvzv4

Clicking cells - run VBA
 

Yesh. :-) I posted a link to an example on the MS template site.

The blood pressure tracker there also uses hyperlinks as "jump links".


On Wed, 26 Jan 2011 18:39:56 +0100, "Harald Staff"
wrote:

Try using hyperlinks. No VBA needed.

Best wishes Harald

"Isis" wrote in message
...
I have a Workbook full of worksheets with bank account details - the first
sheet is an Index sheet with a summary of the various accounts
worksheets -
I want to select the appropriate worksheet and display it when I click (or
maybe double click) the account name on the Index sheet - can I do that ?

Thanks

Clicking cells - run VBA
 

Then i'd suggest you go with the hyperlink solution others brought up.
That's because with the code then, the sheet name isn't enough anymore
- along with the sheet (account) name you'll need to put down in the
index sheet also the workbook name and perhaps also its location
(depending how well structured your file system is).
Of course, tt can be done and perhaps it wouldn't be too much of a
hastle but the Hyperlinks might be just more elegan solution unless
you find it more laboursome.


On Jan 26, 3:10*pm, Isis wrote:
AB wrote :





Right click on the Index Sheet tab (i.e., on the sheet's name (e.g.
Sheet1) and select ViewCode. Then VBE will open.
put this code in the
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
* * On Error GoTo NoSuchSheet:
* * * * ThisWorkbook.Worksheets(CStr(Target.Value)).Activa te
ExitSub:
* * On Error GoTo 0
* * Exit Sub
NoSuchSheet:
* * * * MsgBox "There is no such Sheet in this file!"
* * * * Resume ExitSub:
End Sub

On Jan 26, 2:00*pm, Isis wrote:
I have a Workbook full of worksheets with bank account details - the
firs
t
sheet is an Index sheet with a summary of the various accounts
worksheets
*-
I want to select the appropriate worksheet and display it when I
click (o
r
maybe double click) the account name on the Index sheet - can I do
that ?

Thanks

Thanks AB, that was a help.

If I wanted to link to sheets in other workbooks is there an adaptation I
can make ?

Thanks again- Hide quoted text -

- Show quoted text -