Floating Toolbars

[mworthington]

Hi,

I'm looking for confirmation of something : I often create floating
toolbars (command bars) in my Excel applications. Using any Excel up to
and including 2000, they are truly floating. By this, I mean that
selecting cells near or behind the floating toolbar will not interact
with it. The toolbar remains fixed in place on the screen, as it were.

However, it appears that things are different in Excel 2002/3 ... while
the toolbar can be moved anywhere on the screen, cell selection DOES
interact with the toolbar. Select a cell above the toolbar, then use
the down arrow ... the toolbar appears to be pushed downwards!

I have not found anything about this on the net, so far. Has anyone
else found a resolution? Note that this occurs in all Office products
......!!

I fear that this is a change to how Microsoft thinks we should use
floating toolbars, and it affects how one creates Excel applications
.....

Regards,

Mark

 

[Bernie Deitrick]

Mark,

A sloppy workaround:

In a regular module

Public mybar As CommandBar
Public myTop As Integer
Public myLeft As Integer

In either a regular sub, or the workbook open event:

Set mybar = Application.CommandBars("CBName")
myTop = mybar.Top
myLeft = mybar.Left

The in the Thisworkbook object's codemodule:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
mybar.Top = myTop
mybar.Left = myLeft
End Sub

Of course, this doesn't truly fix the problem - and it causes flickering -
but it will help you. As a user, though, I must say that anytime I see a
floating toolbar, I park it where it won't interfere with my view of the
active workbook.

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Try:

commandbars("Custom 1").Protection = msoBarNoMove

 

[mworthington]

Bernie

Thanks for that. I design my Excel applications with custom toolbars
which are an inherent part of the spreadsheet; they are NOT in the way,
visually, and are important to the user. So it seems daft, for example,
that if the user selects a column and drags the mouse, if the selection
touches a "floating" toolbar, the toolbar moves. That ain't "floating"
in my eyes!

It seems that Microsoft have re-written the rules on this one.

Thanks,

Mark

 

[mworthington]

Tom,

Thanks for that. Unfortunately, of course, the toolbar cannot then be
moved by the user (if/when he wants to)... but I appreciate the advice.
Regards,

Mark

 

[Tom Ogilvy]

Wouldn't you get similar behavior from Bernie's solution?

 

[Bernie Deitrick]

Tom,

Yes, my solution would result in similar behavior.

A work around for this problem using your (much better, by the way) solution
would be to simply add an unlock button on the commandbar labelled "Move
this commandbar" that unlocked the commandbar temporarily - perhaps relocked
using a workbook event.

Bernie
MS Excel MVP

 

[mworthington]

Yes, I use similar techniques to enable a user to set the starting
position of a custom toolbar (as users all have different PC settings
.......)

However, this "floating" issue also applies to built-in toolbars ...

Isn't anyone surprised to see the change that has been introduced in
Office 2002?

Regards,

Mark

 

[Bernie Deitrick]

Mark,

Isn't anyone surprised to see the change that has been introduced in
Office 2002?

No, they change things all the time. Probably received enough "I have to
move those floating toolbars to see my selection" complaints to change the
behavior. I will ask the Excel Developers Group if there is a work around
or other setting option.

HTH,
Bernie
MS Excel MVP

 

[mworthington]

Bernie,

Many thanks!

Yes, I can imagine that happening .....

Regards,

Mark

 

[Rob van Gelder]

You could write a simple lock/unlock toggle button for when the user wants
to move it

 

[mworthington]

Bernie,

17/12/2004 : discussed with Microsoft. This change is "by design". No
resolution, except "to change the way you work".

I have implemented a Lock/Unlock on my custom toolbars ...

Regards,

Mark