+ Reply to Thread
Results 1 to 23 of 23

Double Click cell to jump to sheet

  1. #1
    Registered User
    Join Date
    09-13-2005
    Posts
    59

    Double Click cell to jump to sheet

    hi,

    I have a list of numbers in column B of one sheet.

    these numbers are unique and randomly allocated to other sheets in the workbook.


    i would like a macro which when a user double clicks on a cell in the list of numbers .. the macro will jump to the sheet containing that number.. (i.e. do something like the find function)...

    I have some code below which might be useful for you but I can't work out what I need to write.

    Please Login or Register  to view this content.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    This should get you a bit closer to your goal

    Please Login or Register  to view this content.
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    hey it looks better but still not exactly what i was after...


    the actual cells in column B "list of numbers" are like this

    1
    2
    3
    4
    5
    6


    now when the user double clicks on the cell which contain for example the number 3, I would like the macro to jump to the sheet containing number 3...

    (and if possible to the actual cell containing number 3 in the other sheet)

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Hi neodjandre,

    Will this work?
    Please Login or Register  to view this content.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Why not use hyperlinks?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    roy ... i have about 1,000 numbers in the list...

    is there an easy macro to create hyperlinks ?


    pjoaquin the code doesn't work. I get an error 'subscript out of bounds' with line 'With Sheets(Target.Text)'

  7. #7
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    To create the hyperlinks you would have to either know exactly where the number exists on the other sheet and code it that way, or incorporate a search function into the macro to find it first (similar to what we're doing in the code we've provided for the double-click event).

    Since I'm not sure why you're getting an error, can you zip & post a copy of your workbook, or at least a small example? In my test workbook I simply put the numbers 1 through 10 in column A of Sheet1, then named other sheets 1, 2, 3, 4.. (not Sheet1, Sheet2, etc., just 1, 2, 3, ...). I then inserted the numbers 1 through 10 onto sheets randomly and got the code to work.

  8. #8
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    the problem is that the sheets are not named 1,2,3,4,5 ... but something completely irrelevant :-)

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    This should work

    Please Login or Register  to view this content.

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    Andy

    It might help if you actually told us what these supposedly 'irrelevant' sheet names are.

    If you are having problems it could be something to do with them.

    For example say you've got a sheet named 4, and that's the value in A1 and you only have 3 sheets, this will error.
    Please Login or Register  to view this content.
    The reason for this is that VBA will be regarding the number as the sheet index not the sheet name.

  11. #11
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    norie, the sheets are called John-10, Adam-20... etc and there is no possibility they could be the same with any of the unique numbers


    roy - this works great however is there a way to actually select the unique number in the found sheet instead of range "A1"

    ?

    thanks a lot guys

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Like this

    Please Login or Register  to view this content.

  13. #13
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Put this in a regular code module.
    Please Login or Register  to view this content.
    And this in the code module for your sheet with the big master list.
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    mike your code works the best, however can you limit it to work only for a certain range only e.g. B1:B1000 in the big master list ??

  15. #15
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    Please Login or Register  to view this content.
    VBA Noob

  16. #16
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    Please Login or Register  to view this content.
    thanks, I have tried to modify this a bit further by trying to limit the range only in the used range for column B11:B1511 but this doesn't seem to work.

    any ideas why ?

    cheers
    andy

  17. #17
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    any help with this one ?

  18. #18
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    This modification of VBA Noob's routine should work
    Please Login or Register  to view this content.

  19. #19
    Forum Contributor
    Join Date
    03-24-2005
    Location
    Wisconsin
    MS-Off Ver
    2007
    Posts
    378

    One Approach

    While this does not have 1,000 sheets, it does utilize a scheme you could try.

    While I did not put my sheet names in the cells to be double-clicked, the actual VBA sheet names could be in the cell with the description to the right and set up to open the sheet according to the VBA name shown in the Project Explorer pane of the VBE window. This would, of course, be in lieu of the Select Case scheme this workbook uses.
    Last edited by DCSwearingen; 04-11-2008 at 10:22 AM.
    Thanks!
    Dennis

    I am using Windows 7 and Office 2007, all of my posts are based on this.

  20. #20
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    Quote Originally Posted by mikerickson
    This modification of VBA Noob's routine should work
    Please Login or Register  to view this content.

    Mike,

    could you please test your code. It doesn't work for me as when I double click in empty cells in this range, the macro searches for the empty cells..

    thanks
    andy

  21. #21
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    Yes it does. I thought that findTheCell would handle looking for non-existant values.

    Inserting
    Please Login or Register  to view this content.
    at the start of findTheCell should keep that from being a problem.

  22. #22
    Registered User
    Join Date
    09-13-2005
    Posts
    59
    thanks, that's fine now :-)

  23. #23
    Forum Contributor
    Join Date
    03-24-2005
    Location
    Wisconsin
    MS-Off Ver
    2007
    Posts
    378

    I know this is an old thread, but

    I recently came across the technique for indexing sheets that is in the attached zip file.

    All of the code is in the Index sheet module. You would probably want to remove the part that inserts the hyperlink to return to the index sheet as it will over-write anything in cell A1 on every sheet in your workbook.

    You could also try adding your code for locating the cell range to which you want to locate.
    Quote Originally Posted by neodjandre
    roy ... i have about 1,000 numbers in the list...

    is there an easy macro to create hyperlinks ?


    pjoaquin the code doesn't work. I get an error 'subscript out of bounds' with line 'With Sheets(Target.Text)'
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1