Name Box

[Ron]

I have a very large spreadsheet, which is separated into
regions. I have named each region by highlighting the
range, and giving that range a name. What I want to be
able to do is have a drop down box that allows the user to
select the region they want to view, and then have it jump
to that region (or range) on the spreadsheet.

I attempted a quick workaround by using macros and
buttons, but that varies depending on the settings on the
users machine, and if they are not at the very top of the
sheet, they will sometimes not jump to the right place.

Basically, I need a Name Box that I can label and place in
my worksheet.

Any suggestions?

 

[Bob Phillips]

You already have one. If you goto menu Edit>Goto the Names are listed and
you can select from there.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron]

I also have the Name box directly above the spreadsheet.
What I need to accomplish is getting a dropdown box that
jumps me to a named range (or, basically, a name box that
is part of the spreadsheet so I can label it with
instrcutions). I have a grasp on the functionality...the
users wont. I want to make it easy for them to select a
region, and jump to it.

 

[Bob Phillips]

I still think that it would be better to educate them to use a built-in
functionality rather than build another replication that will

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Ron,

Is there a reason you can't use the name box, on the left side of
the formula bar?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Ron]

I work for a rather large company (30,000+) employees.
Trying to get them to pick a region in the name box will
be impossible (they just wont get it). Training them is
not feasible. I need to put a label on the sheet,
preferably with a combo/drop down box that they can select
the region they are looking for. I can get the combo box
full of the selections (input range)...but that is where I
get stuck. I cannot make it jump to that range of cells.

I wish I could just drag the name box down on the
spreadsheet, as that is exactly what I need.

 

[Chip Pearson]

Drop a combobox control from the Controls tool bar on to your
worksheet. Set the ListFillRange property to your range of names,
and then put the following code in the worksheet's code module.

Private Sub ComboBox1_Change()
Application.Goto Me.ComboBox1.Value, True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com