Remembering a Toolbars Position

If you have toolbars that are customized for each worksheet, you will want to delete and create the toolbar in each worksheet's Worksheet_Activate event procedure. You will find that as the toolbar is deleted and re-created, the toolbar moves down and to the right. It is very annoying to have the toolbar keep moving.

The nice feature about floating toolbars is that clients can customize where they want them to appear. The solution is to remember the location where the client most recently placed the toolbar before you delete it. You can store this on a hidden worksheet in the file:

Public Sub DeleteToolbar() Dim obj as CommandBar On Error Resume Next Set obj = CommandBars("XYZ") If Err.Number = 0 Then ' Remember the current location

With ThisWorkbook.Sheets("Settings") ,[I5].Value = obj.Top .[I6].Value = obj.Left End With End If obj.Delete End Sub

Sub CreateToolbar()

Dim nTop as Variant Dim nLeft as Variant Dim Tbar as Commandbar


With ThisWorkbook.Sheets("Settings") nTop = .[I5].Value nLeft = ,[I6].Value End With

' Define the toolbar Set TBar = CommandBars.Add With TBar

.Name = "XYZ" .Visible = True .Position = msoBarFloating .Top = nTop .Left = nLeft

0 0

Post a comment