asked on

MS Excel - GoTo Command buttons consolidated into a validation list

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.

Andross9

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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
Book1.xlsm

Andross9

ASKER

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.

Andross9

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andross9

ASKER

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

Andross9

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

cheers, teylyn