asked on
Excel 2010: VBA Script to go to a row in a Table based on a cell value
Using: Excel 2010
Given a Table like this but with hundreds of rows:
ID# Title
001 Title1
004 Title4
005 Title5a
005 Title5b
017 Title17
etc.
I would like a user to be able to enter an ID# in a cell (this becomes the lookup value), say "4", and for an adjacent 'GoTo' button, when double-clicked, to move the cursor to the cell in the Table which contains the first occurrence of ID# 004 (in this example the ID#s are non unique but they are numeric as opposed to text; the leading zeros are added by the cell's custom format).
A different variant of this which I also require would, instead of going to the cell which contains the lookup value, filter the Table to show only those records with ID# 004. I realize a user can simply click the Filter arrow under column heading ID#, deselect All and select the desired filter value but this is tedious when many users have to scroll through lists which are hundreds of numbers long, dozens of times a day. Ideally we would have a 'Show' button adjacent to the cell in which the user types in the lookup value and a 'ShowAll' button below that which clears all filter criteria.
Thanks for your help.
Given a Table like this but with hundreds of rows:
ID# Title
001 Title1
004 Title4
005 Title5a
005 Title5b
017 Title17
etc.
I would like a user to be able to enter an ID# in a cell (this becomes the lookup value), say "4", and for an adjacent 'GoTo' button, when double-clicked, to move the cursor to the cell in the Table which contains the first occurrence of ID# 004 (in this example the ID#s are non unique but they are numeric as opposed to text; the leading zeros are added by the cell's custom format).
A different variant of this which I also require would, instead of going to the cell which contains the lookup value, filter the Table to show only those records with ID# 004. I realize a user can simply click the Filter arrow under column heading ID#, deselect All and select the desired filter value but this is tedious when many users have to scroll through lists which are hundreds of numbers long, dozens of times a day. Ideally we would have a 'Show' button adjacent to the cell in which the user types in the lookup value and a 'ShowAll' button below that which clears all filter criteria.
Thanks for your help.
VB ScriptMicrosoft ExcelSpreadsheets
Last Comment
Martin Liss
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Sorry for the delay; just able to get back to this now.
Steve: thanks, your solution does work and has the benefit of leaving the original (full table) intact, so I don't have to filter and unfilter. The part I'm not clear on though is: am I right in assuming that for this to work, the validation list in the rows added above the main table needs to be built with the ID#s. For example, if I added a row to your example, with ID#010, the 'filter' validation list in the rows above doesn't autoupdate. My problem here is that my lists are hundreds of items long and are dynamic (the id #s change, some get added, some get deleted)
Steve: thanks, your solution does work and has the benefit of leaving the original (full table) intact, so I don't have to filter and unfilter. The part I'm not clear on though is: am I right in assuming that for this to work, the validation list in the rows added above the main table needs to be built with the ID#s. For example, if I added a row to your example, with ID#010, the 'filter' validation list in the rows above doesn't autoupdate. My problem here is that my lists are hundreds of items long and are dynamic (the id #s change, some get added, some get deleted)
ASKER
FarWest:
I wasn't able to get the code you provided to run (I was careful to check to make sure I was putting a value in cell $A$3 to trigger "Worksheet_Change(ByVal Target As Range)". Putting a value in that cell, changing it, hitting enter, or double-clicking wouldn't trigger the macro. I tried making the macro public and to run it explicitly, by name, but the marco wouldn't show up.
I did develop some alternative code which does what I need though. I'm able to filter the table to a value I type in, in a given lookup cell. I'm also able to unfilter the table if i delete the value in the lookup cell.
What I need to do now is to open the hyperlinked filename in the second column of my Table (in my example above, assume that the Titles are hyperlinked) based on the value of 'lookup' in the first row.
So in my example, with a user typing "5" in the lookup cell, the macro would cause the table to autofilter to just the row with ID# 005 (that part I have working) and would open the hyperlinked filename "Title5a" from the adjacent cell to the right of ID#
I wasn't able to get the code you provided to run (I was careful to check to make sure I was putting a value in cell $A$3 to trigger "Worksheet_Change(ByVal Target As Range)". Putting a value in that cell, changing it, hitting enter, or double-clicking wouldn't trigger the macro. I tried making the macro public and to run it explicitly, by name, but the marco wouldn't show up.
I did develop some alternative code which does what I need though. I'm able to filter the table to a value I type in, in a given lookup cell. I'm also able to unfilter the table if i delete the value in the lookup cell.
What I need to do now is to open the hyperlinked filename in the second column of my Table (in my example above, assume that the Titles are hyperlinked) based on the value of 'lookup' in the first row.
So in my example, with a user typing "5" in the lookup cell, the macro would cause the table to autofilter to just the row with ID# 005 (that part I have working) and would open the hyperlinked filename "Title5a" from the adjacent cell to the right of ID#
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
you can add this to sheet code that will filter if that value changed
Open in new window