Navigation Through spreadsheet using short cuts - not range names.

[garretonufer]

I currently have a navigation menu with 9 options however these options are based on Range names that I have set up.

The concern is that if someone deletes the cell that contains the range name, obviously that option in the navigation won't work, nor would you want it to.

However if someone re-enters a cell that you would want linked up with the navigation there's a lot more action involved.

What i'm looking for is a macro that can be used through a button,
Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.)
and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")

what's the best way to accomplish this???

[Palmetto]

You're post is not clear (at least to me).

Thread title indicates you want to navigate sheets using "short cuts", but you indicate that you want to search in a column.

Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.)
and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")

Please clarify exactly what you want to do.

[garretonufer]

I want to search through a column to find the target location in which to go to using a short cut.

what i'm doing now is a Range("Ground").Activate
and that will select the desired cell on the spread sheet, allowing me to navigate throughout the spread sheet.

So I want to do the same thing, although instead of using Range names, i want the target to be found through a search of values in column "B"

[shg]

What's in column B?

[garretonufer]

Note that in Columb "B" there are main labels
"Ground Floor"
"1st Floor"
"2nd Floor"
and so on


so if i have a button with a macro, i would want the button labeled "Ground Floor" to search through column "B" for "Ground Floor" and Activate that Cell.

so that if i'm somewhere on the spreadsheet I can click on the "Ground Floor" button and it will take me to where ever the cell with the value of "Ground Floor" is.

[shg]

You can do that with a formula:

=HYPERLINK("#B" & MATCH("Ground Floor", $B:$B, 0), "GND")

[garretonufer]

How do i do that in a macro?
I am going to delete the hyperlinks on the page and make a user form that contains 9 buttons. Ground Floor, 1st Floor - 8th Floor

[shg]

Please Login or Register  to view this content.

[garretonufer]

Thank you much. Works like a charm,

now a curve ball, say "Ground Floor" isn't in the file (ie: it was deleted) how hard would this be to have it conditional, and generate a MsgBox if the Target text is non-existant?

[shg]

Please Login or Register  to view this content.

[garretonufer]

That worked like a charm. You're amazing.

Thanks to everyone for your help.