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

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

wrote in message
oups.com...
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

Try:

commandbars("Custom 1").Protection = msoBarNoMove

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

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

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.

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

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

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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,

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

"Tom Ogilvy" wrote in message
...
Wouldn't you get similar behavior from Bernie's solution?

--
Regards,
Tom Ogilvy

wrote in message
oups.com...
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

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

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.

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

Bernie,

Many thanks!

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

Regards,

Mark

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.

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

--
Rob van Gelder - http://www.vangelder.co.nz/excel


wrote in message
oups.com...
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

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,

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

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.