MS Excel - GoTo Command buttons consolidated into a validation list

Posted on 2011-05-02
Last Modified: 2012-05-11
I have in excess of ten GoTo command buttons that I would like to consolidate into a single button using a validation list or similar approach.

Am currently using
Application.Goto Reference:=Range("V99:AB112"), Scroll:=True

The data for the GoTo is below:

Methodology                             CZ3000
Coverage Summary      DD3103
Classification Summary      DF3204
Public Holidays      DS3408
Payrate Chronology       DV3511
Comments                             ED3615
Penalty Summary      EH3718
Allowances                             FF4718
Other Conditions      FS5772
Annual Leave      FV5926
Personal Leave      GK6155
Parental Leave      HA3695
Long Service Leave      HU6622
PILN                             IN6841
Redundancy                             JC6940
Disagreggation of Super from Base Rate      JR7039
Disagreggation of Annual and Personal Leave from Base Rate      KQ7120
Calculation of Commission      LQ7200
Calculation of Superannuation      LW7240

Any ideas would be appreciated.

Question by:Andross9
    LVL 50

    Expert Comment


    Your Goto Reference range contains a range of several columns and rows.

    What is the data below that? Are these the goto reference ranges for each choice? I.e. when
    "Methodology" is selected,, should the active cell change to cell CZ3000? Just a single cell?

    please advise.

    cheers, teylyn
    LVL 50

    Expert Comment

    If the above codes are meant to be single cell references, you could use data validation to select a value as listed in column A, then use a lookup to determine the cell address as listed in column B and use a macro to jump to the cell address.

    See attached file.

    cheers, teylyn

    Author Comment

    Hi Teylyn

    Yes the data is below and to the right of the cell reference.

    Wanting to have the cell reference as the active cell and displayed in the top left corner of the display.

    I can give broader ranges but in some cases the ranges are too wide or long to fit to a screen.

    Thanks for your prompt reponse.

    LVL 50

    Accepted Solution

    In that cased, check out my previous post. It must have crossed over with yours.

    There are other ways to skin this cat, if the method suggested does not suit.

    cheers, teylyn

    Author Closing Comment

    Yes we crossed over.

    The solution works just as needed.

    I really appreciate your ongoing efforts to help others.

    Have recently completed some Excel and VBA courses but still struggling at context of some of the basic frameworks.

    Great stuff.

    Thanks again

    LVL 50

    Expert Comment

    Thanks for the grade. Getting your head around it all can take a while. We're all still learning every day.

    cheers, teylyn

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now