Clicking cells - run VBA

  • Thread starter Thread starter Isis
  • Start date Start date
I

Isis

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
 
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 there:
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
On Error GoTo NoSuchSheet:
ThisWorkbook.Worksheets(CStr(Target.Value)).Activate
ExitSub:
On Error GoTo 0
Exit Sub
NoSuchSheet:
MsgBox "There is no such Sheet in this file!"
Resume ExitSub:
End Sub
 
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 there:
Option Explicit

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

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
 
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
Click to expand...

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
 
Yesh. :-) I posted a link to an example on the MS template site.

The blood pressure tracker there also uses hyperlinks as "jump links".
 
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.
 
Back
Top