File window tops slip up under toolbars in Excel 10.1.5--bug?

[Norman R. Nager, Ph.D.]

I just trashed the three preferences for Excel after trying without success
to figure out how to keep the window tops of new workbooks or previously
saved files from slipping up under the toolbars.

The trashing and re-setting of preferences is a bit tedious. More tedious
by far is moving each of the toolbars to the right so one can access the
top of the file window and have something to drag down and also hiding the
dock so one can get to the re-sizer in the lower right-hand corner of a
worksheet window.

Is this a bug in Excel X that emerges from time to time or is there some way
to prevent it or understand what causes it?

Thanks for any light you can shed on this issue.

 

[Bernard REY]

Norman R. Nager, Ph.D. wrote :

I just trashed the three preferences for Excel after trying without success
to figure out how to keep the window tops of new workbooks or previously
saved files from slipping up under the toolbars.

The trashing and re-setting of preferences is a bit tedious. More tedious
by far is moving each of the toolbars to the right so one can access the
top of the file window and have something to drag down and also hiding the
dock so one can get to the re-sizer in the lower right-hand corner of a
worksheet window.

Have a closer look to the way your toolbars are arranged. They probably
don't correctly "stick" together. And if they aren't docked to the top, they
are considered (by Excel) as "floating bars" rather than docked bars.

I think that you can easily solve this by making sure the bars are docked at
the top. But the line isn't always too visible, especially if it's the first
bar (the "Standard" bar?) that's not docked. Simply try moving the bars up
to the top, you'll certainly see how it works.

 

[Harvey Waxman]

Norman R. Nager, Ph.D. wrote :


Have a closer look to the way your toolbars are arranged. They probably
don't correctly "stick" together. And if they aren't docked to the top, they
are considered (by Excel) as "floating bars" rather than docked bars.

I think that you can easily solve this by making sure the bars are docked at
the top. But the line isn't always too visible, especially if it's the first
bar (the "Standard" bar?) that's not docked. Simply try moving the bars up
to the top, you'll certainly see how it works.

I have the same experince as Dr. Nager. I was very careful to let the toolbars
snap to where they want to be and indeed the sheets will slip under them. The
only thing I could find to do was to be careful to leave a small space between
the toolbars so I'd have something to grab.

As for the resize issue I find that keeping the tool bar on the right side of
the screen is more convenient for me. I doesn't interfere with the window
corner that way.

Harvey

 

[Bernard REY]

Harvey Waxman wrote :

I have the same experince as Dr. Nager. I was very careful to let the toolbars
snap to where they want to be and indeed the sheets will slip under them. The
only thing I could find to do was to be careful to leave a small space between
the toolbars so I'd have something to grab.

You don't mention which version of Excel is concerned. Make sure it is
updated to 10.1.5 (there has been some kind of a bug of that sort on the
very first version, if I remember well).

But if you have already applied the updates, make sure the bars (and
especially the first one) are "docked" at the top. If they are just a
(hardly noticeable) little bit too high, or too low, they may "seem" to be
docked but aren't.

As for the resize issue I find that keeping the tool bar on the right side of
the screen is more convenient for me. I doesn't interfere with the window
corner that way.

Yes, and it doesn't matter if they are straight on the left side or not when
they are docked too.

I never since long encountered the problem you both describe (except from
time to time after a VB macro crash)...

[/QUOTE]

 

[Harvey Waxman]

You don't mention which version of Excel is concerned. Make sure it is
updated to 10.1.5 (there has been some kind of a bug of that sort on the
very first version, if I remember well).
10.1.5

But if you have already applied the updates, make sure the bars (and
especially the first one) are "docked" at the top. If they are just a
(hardly noticeable) little bit too high, or too low, they may "seem" to be
docked but aren't.

They are docked. I'm a sailor after all and I know how to dock

 

[Jim Gordon MVP]

Hi Norman,

This little Visual Basic subroutine should cause your workbooks to open
maximized and they should properly fill the screen:

Sub Autpen()
'Open maximized on both Mac and Windows
If Application.OperatingSystem Like "*Mac*" Then
ActiveWindow.Zoom = 100
With ActiveWindow
.WindowState = xlMaximized
.Top = 1
.Left = 1
.Height = Application.UsableHeight
.Width = Application.UsableWidth
End With
Else
With ActiveWindow
.WindowState = xlMaximized
End With
End If
End Sub

It's very easy to install this code into a workbook.

1. Copy the code from this message (copy everything starting with SUB
through END SUB), then switch to Excel
2. From Excel's Tools menu choose Macro > Visual Basic Editor
3. From the editor's menu choose Insert > Module
4. Paste the code into the module window
5. Press Apple+Q to return to your workbook
6. Save the workbook

The next time you open the workbook it should position itself nicely on the
screen.

If this is the first time you've done anything with visual basic, now you
know it is not terribly hard to use. You can learn a lot about visual basic
from this URL:
<http://support.microsoft.com/default.aspx?scid=/support/macexcel/content/Ma
cVBA/MacVBA.asp>

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

 

[Norman R. Nager, Ph.D.]

Thanks very much, Mr. Rey. Tightly docking the toolbars to the top and
making sure there is no space between them solves the problem! May I
suggest that this be added to the Help program and FAQs? Respectfully,
Norm

 

[J.E. McGimpsey]

I have the same experince as Dr. Nager. I was very careful to let
the toolbars snap to where they want to be and indeed the sheets
will slip under them. The only thing I could find to do was to
be careful to leave a small space between the toolbars so I'd
have something to grab.

As for the resize issue I find that keeping the tool bar on the right side of
the screen is more convenient for me. I doesn't interfere with the window
corner that way.

JFI, I use a macro in my Personal Macro Workbook to set the Standard
and Formula Toolbars at the top and lock them in place so that they
can't become undocked.

See

http://www.mcgimpsey.com/excel/locktoolbars.html

 

[Harvey Waxman]

JFI, I use a macro in my Personal Macro Workbook to set the Standard
and Formula Toolbars at the top and lock them in place so that they
can't become undocked.

With the windows docked, as I believe mine are, I can still drag a window too
high, accidentally of course, and then be unable to grab it again after
releasing it. I leave a small space between the windows along the top, just in
case.

 

[J.E. McGimpsey]

With the windows docked, as I believe mine are, I can still drag
a window too high, accidentally of course, and then be unable to
grab it again after releasing it. I leave a small space between
the windows along the top, just in case.

You could also close the gap and use Window/Arrange...

If you check the Windows of Active workbook checkbox, that window is
placed back below the toolbars without disturbing those from other
books.

 

[Norman R. Nager, Ph.D.]

Thanks very much, Jim.

This works like a charm!

1. Could it be made into a keyboard macro so one wouldn't have to repeat the
steps with each previously saved workbook?

2. Does Excel have the equivalent of Word "Normal" documents that one can
modify with such a VBA sub-routine?

Respectfully, NormN

 

[Norman R. Nager, Ph.D.]

Thanks very much, J.E., for sharing the macro.

I got confused after I reached the part of the instructions that said:

"Then call the macro from the Workbook_Open() event code in the ThisWorkbook
code module of your Personal Macro Workbook:
Private Sub Workbook_Open()
LockToolBars
End Sub

I copied and pasted directly into the module the first part of the script
and then went back in and pasted in the three additional lines.

I vaguely recall at one point doing an Apple-q combination keyboard command.

The above three lines of script appeared in the macro, divided from the rest
by a thin rule.

I'm not sure what happened, but after I saved it in the Startup/Excel folder
and then re-started Excel, the top toolbar was crunched up part way under
the Excel menubar and my other two toolbars overlapped each other.

I then trashed what I created.

Do you have any thoughts on what I might have done wrong?

Thanks, NormN

 

[J.E. McGimpsey]

1. Could it be made into a keyboard macro so one wouldn't have to repeat the
steps with each previously saved workbook?

If you rename the code, you can use XL's Tools/Macro/Macros dialog
to assign a keyboard shortcut - click Option and enter your
preferred combination.

2. Does Excel have the equivalent of Word "Normal" documents that one can
modify with such a VBA sub-routine?

Not really. XL templates are not attached to workbooks the way Word
templates are attached to Word documents. New workbooks are simply
copies of their template and have no further interaction with them.

That said, you can modify the base template for all new workbooks by
setting the code, formatting, number of worksheets, etc. that you
desire in new workbooks in a fresh workbook. Then save it as a
template, with the name "Workbook" (no quotes, no extensions) in the
Microsoft Office N:Office:Startup:Excel: folder (where N is the
version).

This has one drawback - the workbook_open() code that sets your
particular preferences will be copied to each workbook you create.
If you send your workbooks to others, the macro will run on their
computer as well, which may not be what they prefer.

I give a method to set the window position, size and zoom for each
workbook that opens on your machine (only) at:

http://www.mcgimpsey.com/excel/windowsetup.html

 

[J.E. McGimpsey]

I got confused after I reached the part of the instructions that said:

"Then call the macro from the Workbook_Open() event code in the ThisWorkbook
code module of your Personal Macro Workbook:
Private Sub Workbook_Open()
LockToolBars
End Sub

I copied and pasted directly into the module the first part of the script
and then went back in and pasted in the three additional lines.

I vaguely recall at one point doing an Apple-q combination keyboard command.

The above three lines of script appeared in the macro, divided from the rest
by a thin rule.

I'm not sure what happened, but after I saved it in the Startup/Excel folder
and then re-started Excel, the top toolbar was crunched up part way under
the Excel menubar and my other two toolbars overlapped each other.

I then trashed what I created.

Do you have any thoughts on what I might have done wrong?

I don't thing you did anything wrong - the .Left = 0 statements
seemed to cause the positioning problem. I've got a slightly
different sequence in my setup file, so I never noticed (and it
didn't show up when I tested in XL01).

Take out both .Left = 0 statements (I've corrected the web page, so
you can copy from there again). I tested it and it works correctly
for me.

Please let me know how it works for you now.

 

[Scott Melendez]

I have a similar problem, except that EVERY time I open Excel ­ even a
previously saved file ­ the workbook stretches down to the Dock. So I wind
up having to hide the dock or move the sheet until I can grab it and re-size
it.

Any suggestions?

--
Best Regards,

Scott Melendez



With the windows docked, as I believe mine are, I can still drag a window too
high, accidentally of course, and then be unable to grab it again after
releasing it. I leave a small space between the windows along the top, just
in
case.
[/QUOTE]