Name Drop-Down Box

[cbh35711]

I've got a 50 sheet excel document and i want to create names to get m
to A1 on every page, so I don't have to scroll left and right all th
time.
So, here's my problem.
I would like to increase the number of names shown when i click th
drop down box. Right now it shows 7, in Office 2003. I would love t
increase that to show 50, or else I’m not really getting th
functionality I’m looking for.
I think this will take some kind of registry tweak.
Does anyone know how/where I can?

Thanks in advance,

Chri

 

[Dave Peterson]

Ctrl-Home should take to to the top left cell (usually A1).

But you could also define a name that refers to A1:

Insert|Name|Define
Names in Workbook: Home
(that was the name I used)
Refers to:
=indirect("a1")

Then no matter where I was, I could hit F5, type Home and hit enter--and off to
A1 of the activesheet.

 

[cbh35711]

I know how to create a name, that wasn't my question...
A1 isn't what i'm looking for, it's to travel to any sheet in my file,
without having to scroll left and right, on the bottom, or up and down
on the names list. I want to make the names list list more names than
it does now.
Anyone know how?

Thanks,

Chris

 

[Dave Peterson]

I read your question as asking how to get to A1 of the current sheet without
scrolling right or left or up or down.

But you meant you want to go to A1 of any sheet by selecting the name that's
assigned to A1 of that sheet.

I don't know a way of expanding the Namebox, but you could use a floating
toolbar:

http://contextures.com/xlToolbar01.html
from Debra Dalgleish's site.

Modify this portion:

With ctrl
.Width = 300
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
.DropDownLines = 50 '<--added
End With

And change this portion:

If wks Is Nothing Then
Call RefreshTheSheets
MsgBox "Please try again"
Else
wks.Select
wks.Range("a1").Select '<-- added
End If