Movable Toolbar – frustrating (Excel 2002)

Topic

New Reply

I have been using Excel 2002 since May. Many of the features I like, but I have run across one that is very frustrating. I have developed several macros for a project in which I use 78 other worksheets; I put these macros in a new toolbar and position it on the right side of the screen for easy reach (speed, access, consistency of moving the cursor, etc.). However, occasionally I have to copy some rows from on some of the projects worksheets. (I don’t have a macro for this copying work because the determination to copy is dependent on several factors and is very inconsistent; some worksheets need it, some do not, others need it but not every quarter.)

Anyway, when copying, say rows 1-25, the toolbar on the right moves either up or down depending on which direction I move the mouse. In effect, the copying procedure pushes the toolbar out of the way.

I have found no way to diable this “feature” and it is very annoying because I have to reposition the toolbar. Am I missing something? Or do I have to live with another MS “feature”?

Reply | Quote

Replies

Either this is something new with versions >XL97 or you are not speaking about a toolbar.

In XL97 when I park a toolbar on top of the spreadsheet, it stays locked in that spot. It has no ties at all to any of the spreadsheets. Inserting, deleting, resizing, scrolling have NO effect on its placement.

Reply | Quote

Following on from Steve’s advice, it sounds more like you have a series of buttons on the spreadsheet. For each button you may need to set Format Control to Don’t Move or Size with Cells, but the better way to go is to create a true ToolBar in accordance with Jan Karels post 200526.

Reply | Quote

Well, since I never put toolbars on the right, I fired up Win/XP XL/XP and docked a large toolbar (Auditing) on the right. The toolbar stays put, no matter what. The only thing that I noticed was a nice feature: it remembers which side of the TaskPane you have docked the toolbar. So, you can display the TaskPanel, dock a toolbar on the left, hide the TaskBar, and when the TaskBar comes back, your toolbar stays on the left. Guess you’ll have to give us more details. –Sam

Reply | Quote

I’m not sure what is happening. I had no problems with this when using Excel 97 on Windows 95. Using Excel 2002 on Windows 2000, the toolbars move – never did with the old set up. And this is a toolbar, not a button, set up exactly the same way in Excel 97. The problem with attaching this toolbar to the worksheet is that I have to do it to all 78 workbooks.

I just tried the standard toolbars that are always available. When I pull them off the top, then select rows (on the left side), then selecting process moves the tool bar down or up, depending on which way I select the rows. Same is true if I select columns. The tool bars move right or left depending on the direction of selecting.

Of course, if I dock it solidly on the right side, then it doesn’t move. The problem is that my descriptive tags for each macro then runs up and down – making it difficult to read.

Reply | Quote

I’m confused by this answer, but (straight from Help) have you set the CB positions, one of the following MsoBarPosition constants: msoBarLeft, msoBarTop, msoBarRight, msoBarBottom, msoBarFloating, msoBarPopup, or msoBarMenuBar, like so:

For Each bar In CommandBars
If bar.Visible = True Then
If bar.BuiltIn Then
bar.Position = msoBarTop
Else
bar.Position = msoBarRight
End If
End If
Next

Perhaps you can attach an example?

Reply | Quote

But, he gets it with standard toolbars!

Reply | Quote

Then I (pun intended) don’t get it!

Reply | Quote

I have attached a Word file that has a screen shot of Excel. This is only an example. I placed the toolbar so that the upper left corner of it was above cell F6. Then I went to the left side and began selecting rows 5-11. As I got to row 6, where the toolbar was, the toolbar began sliding down to its present position, as I continued selecting rows.

Am I making sense?

Reply | Quote

No, I’m still not clear. The toolbar should never move relative to the window, no matter if it is 97, 2K or XP. It your example it is 3 inches from the top and 3 inches from the left. It should never move from this position, but it floats over the worksheet, so it could be over almost any cell.

Reply | Quote

Well, no matter which toolbar I use, even the standard ones, if I move them away from the docking position, then the toolbar will move everytime I select rows or columns ((and even just a range of cells, it doesn’t have to be the entire row) that adjoin where the toolbar is. In other words, it is “floating” only to the extent that I can move it around. But once I select row/columns/cells, then the toolbar tries to “move out of the way” – The toolbar acts as if a snowplow is pushing it. Once it gets to the top, it will jump over the standard tool bars, to the very top of the screen, but not docked.

I never had this issue with Excel 97 (or at home with Excel 2001 on a Mac); but it has been there since day 1 of Excel 2002.

Reply | Quote

Hey all,

You’ve got him outnumbered, but he is not alone.
I have both Excel 97 and Excel 2002 installed on my system.
In my Excel 97 – floating toolbars do not move when adjacent rows are selected.
In my Excel 2002 – they move with the selected rows.

An interesting find was this quote from XL2002 help:

“Turn on or off transparent floating toolbars
On the View menu, point to Toolbars, and then click Customize.
Click the Options tab.
Under Other, clear or select the Transparent floating toolbars check box. ”

Of course the check box they refer to is not there.

Regards,

Jim Cone
San Francisco, CA

Reply | Quote

Yep, Jim. I found that same Help topic on Monday night. Went to customize, and sure enough that dialog option for transparent toolbars is not there.

Thanks, Jim, at least I know I’m not nuts.

BTW, I checked with others in our corporation and they have the same “problem.” The corporation has 75,000+ employees, so it must be a definite “feature” that doesn’t match with the Help file.

Reply | Quote

Hi,
I just wanted to add that my toolbars exhibit the same behaviour. I assume it’s a feature to ensure that they’re never in the way of what you’re working on. The only way around it I know of (without docking it) is to set the Commandbar’s Protection property to msoBarNoMove but of course that will prevent the user from being able to move it either.
FWIW.

Reply | Quote

Maybe the option can be changed by macro? (Haven’t got XP, so no sample code, I’m sorry)

Maybe the option can be set in Word? Some of these are office-wide settings.

Reply | Quote

It looks as if this behaviour is tied into the nasty little paperclip that comes with office!
If I show the office assistant and set his option to

Reply | Quote

But the very first thing I did after installing Office XP software was to get rid of the Office “Assistant” – i.e. I hide the “Assistant”; but the behavior (of the Toolbar moving) still occurs.

Reply | Quote

Have you tried bringing him back and checking how the option is set? It might still apply with him hidden in XP.

Peter

Reply | Quote

Thanks, Peter. Yes, I tried that, and any other combination of the above. While the Office Assistant responds to changes in these options, the toolbars do not.

Reply | Quote