Name Box Cell Address

  • Thread starter Thread starter norika
  • Start date Start date
N

norika

It may be a silly question!

When selecting A1, name box will show the address A1. Is it possibl
not to show the cell address in the name box when selecting the cell?

norik
 
Don't know if you can keep the address from showing, but you can keep
the Name Box from displaying:

Public Sub ToggleNameBox()
With Application.CommandBars("Formula Bar").Controls("Name Box")
.Visible = Not .Visible
End With
End Sub
 
Thank you for your reply.

Other than writing Macro, is there any other methods not to show th
name box cell address when selecting?

Thank in advance.

norik
 
JE McGimpsey,

Are you using XL2003?
I cannot find a "Name Box" control in either XL97 or XL2002.

Regards,
Jim Cone
San Francisco, CA
 
Norika, JE and Jim,

I am afraid I missed something.

What is the possible use of hiding the name box showing the cell address or
preventing the showing of the cell address if one can "see" (literally with
ones own eyes) which cell is activated?

Jack Sons
The Netherlands
 
Jack:

If the active cell is being controlled via VBA and the screen is fixe
(e.g. by scroll area or Application.Screenupdating=false) then "it al
happens" somewhere where you can't see it. If there is a cunning cel
somewhere, it's a big giveaway to have its address on the fron
page...

Al
 
I have no idea why norika wants the name box not to show the address,
but one can only "see" which cell is activated, with one's own eyes, if
the active cell is in the visible window.

If a cell is activated programmatically, there's an option of scrolling
so that the cell is visible, or not. Or, if you select a cell manually,
say IV65536, you can scroll back to A1 without changing the selection,
and the selected cell will no longer be visible.
 
This didn't work for me in xl2002?

Maybe it's one of those Mac things--soon (ahem) to be included in windows excel
(maybe it was added in xl2003???).
 
Are you trying to hide the addresses of a few cells.

If yes, then maybe you could just name the cell (insert|Name|define) to
something like:
"DONTLOOKHERE"

The users would still be able use that name in Edit|goto (say) to find out where
it was, though.

But I think it would stop most of the users I've seen.

And if you could use some code, you could even assign a name on the fly and then
delete it when you get off that cell.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Me.Range("a1")) Is Nothing Then
On Error Resume Next
ThisWorkbook.Names("DONTLOOKHERE").Delete
On Error GoTo 0
Else
Target.Name = "DONTLOOKHERE"
End If

End Sub

But this, too, won't stop all.

If you want to try it, right click on the worksheet tab that should have this
behavior and select view code. Paste this into the code window (usually on the
right).

Then back to excel and test it out.

Heck, you could even have it name each cell as you change your selection:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Name = "DONTLOOKHERE"
End Sub

Try it this way:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Name = "_____________"
End Sub

It looks almost empty!
 
You're kidding, right? (I've never had a use for hiding it, but my MacXL
startup add-in uses

CommandBars("Formula Bar").Controls("Name Box").Width = 150

to get a little elbow room in the name box. I never suspected it didn't
work in WinXL (which I use primarily for testing).

Guess I'll have to go check XL03...

(And as far as "soon to come"... I think you'll be pleased with what
MacBU has come up with in MacXL2004 for the Win side to copy...)
 
Resizing the namebox that way doesn't seem fair.

In wintel land, you could use Robert Gelb's NameBoxResizer:
http://www.vbrad.com/pf.asp?p=Source/src_xl_nb_addin.htm
(for xl2k and above, but there's a link that points to an xl97 version)

or Chip Pearson's code:
http://www.cpearson.com/excel/NameBox.htm

Both are a little less intuitive than ".width = 150" <bg>.

I wonder when the good stuff will be copied. I know I can copy|paste from the
ng's pretty fast. I would think that MS employees would be able to use ctrl-c
and ctrl-v even quicker. <gd&r>
 
OK, I do remember Chip's code now.

On the other hand, we MacXL users don't have the benefits of a Task pane!
 
Back
Top