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

Post your code, but I suspect you are not deleting the Toolbar when the
workbook closes.

See code at Debra Dalgleish's site for creating and deleting a toolbar.

http://www.contextures.on.ca/xlToolbar02.html


Gord Dibben MS Excel MVP

On Wed, 22 Oct 2008 14:26:04 -0700, 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

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

You are correct, I do not delete my toolbar upon closing my workbook. I was
allowing users to place the toolbar where they want. I didn't want them to
have to move it each time they opened the workbook. I will take a look at
making it temporary.
--
Thx
MSweetG222



"Gord Dibben" wrote:

Post your code, but I suspect you are not deleting the Toolbar when the
workbook closes.

See code at Debra Dalgleish's site for creating and deleting a toolbar.

http://www.contextures.on.ca/xlToolbar02.html


Gord Dibben MS Excel MVP

On Wed, 22 Oct 2008 14:26:04 -0700, 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

JAC,

You and Gord are correct, my toolbar is not temporary. I was hoping that
the users could place the toolbar where they wanted and it would be there
when they opened the workbook again.
--
Thx
MSweetG222



"JAC" wrote:

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

Hello All,

I am going to add this for documentation purposes.

Here is a sample of my code line:
Set cbCustomMenu = cbMainMenuBar.Controls.Add(Type:=10, Befo=iHelpMenu,
Temporary:=True)

Even though my CustomMenu's Temporary parameter is set to "True". The
"Temporary" parameter is supposed to remove the CustomMenu as Excel closes.
While that may be happening, Excel must still be recording the CustomMenu in
the XLB file, because the XLB increased by 3KB. At 3KB per session of Excel,
this will bloat the XLB file fairly quickly. Since I am using Excel in the
Task Schedule every 5 minutes, it will bloat faster than normal.

So I had to modify to the Auto_Close procedure to include this line of code
....

Application.CommandBars("Worksheet Menu
Bar").Controls("&CustomMenuName").Delete

Actually deleting the CommandBar before the Excel workbook is close actually
prevents the XLB bloat issue. I don't know if this is the right answer, but
it certainly solves the bloat issue.

--
Thx
MSweetG222



"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

Which you were told to do October 22nd when you first posted.

From my reply to you then......................

Post your code, but I suspect you are not deleting the Toolbar when the
workbook closes.


Gord Dibben MS Excel MVP

On Mon, 27 Oct 2008 12:07:35 -0700, MSweetG222
wrote:

So I had to modify to the Auto_Close procedure to include this line of code

Thank you JAC, I will try.
--
Thx
MSweetG222



"JAC" wrote:

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

Sorry, I didn't remember I had a custom toolbar AND an item on Excel's
existing worksheet toolbar. I thought my custom toolbar was the issue because
I did not set it as "temporary", but it was the item I added to Excel's
worksheet toolbar.
--
Thx
MSweetG222



"Gord Dibben" wrote:

Which you were told to do October 22nd when you first posted.

From my reply to you then......................

Post your code, but I suspect you are not deleting the Toolbar when the
workbook closes.


Gord Dibben MS Excel MVP

On Mon, 27 Oct 2008 12:07:35 -0700, MSweetG222
wrote:

So I had to modify to the Auto_Close procedure to include this line of code