On 22 Oct, 22:26, MSweetG222
wrote:
Hello all,

I have written an workbook with its own custom toolbar. *I have attached the
workbook to task schedule and it runs every 5 minutes. *No issues. *

However, I have recently noticed that everytime the workbook opens/closes,
my XLB file increases in size by 3KB each time. *As you can image, the XLB
file can become extremely large in month. *I know I can delete XLB and "start
over", however, I was planning on giving the macro workbook to others and I
didn't want to have to remember to "clean up" their XLB every month. *

I tested the opening and closing of Excel (NOT using my macro workbook) and
it increases the XLB by 1KB every 3 or 4 times a workbook is *open.

Is there some kind of newbie thing I did when writing my macro or creating
my custom toolbar that is increasing the XLB so quickly? *In my macro, I do
"test" upon opening of the macro workbook to see if my custom toolbar exists
(and if it does not, I create it) and then the "auto open macro" attaches the
proper macro to the individual custom toolbar icons.

Any help would be greatly appreciated.

MSweetG222

Try something like this, where you add temporary:=True while adding
the control. It creates a temporary facility that dies when you close
your workbook.

Private Sub AddHelp()
Dim cbWSMenu As CommandBar
Dim mnuXL As CommandBarControl

' Attach a temporary option to the Help menu for the application

On Error Resume Next

Set cbWSMenu = Application.CommandBars("Worksheet Menu Bar")

If Not cbWSMenu Is Nothing Then
Set mnuXL = cbWSMenu.Controls("Help")

If Not mnuXL Is Nothing Then
With mnuXL
With .Controls.Add(Type:=msoControlButton,
temporary:=True)
.Caption = "&XLHelp"
.OnAction = "XLHelp"
End With
End With

Set mnuXL = Nothing
End If

Set cbWSMenu = Nothing
End If
End Sub