Hi:

I have a wired problem with my Macro I generated, (running in Excel
2000)
This macro is copied to XLStart Folder. and I have this macro
installed on 3 user's computers. However on one of the user, my custom
menu and toolbar disappears from time-to-time:

This is how the process works:

There is an external web base software application user will run and
this software generates their reports in Excel format. So when user
runs reports for multiple accounts, it creates these reports and
loaded in Excel (same layout except different info on each excel file)

This application also has a custom menu, so WHEN USERS SWITCH TO THESE
EXCEL REPORTS, THEY WILL SEE THE CUSTOM MENU FROM THAT APPLICATION AS
WELL AS MY CUSTOM MENU AND TOOLBAR.

Only one user has a problem, NOT DURING THE INITIALLY, only after she
closes few excel reports, then at some point MY CUSTOM MENU AND
TOOLBAR DISAPPEARS TOO.

THE MOST WIRED PROBLEM IS THAT THIS IS NOT CONSISTENT, SOME DAY IT'S
OK AND SOME DAY IT'S NOT. AND THE OTHER 2 USERS HAD NEVER EXPERIENCE
THIS PROBLEM.

This is really wired and I did checked, all the users has the same
excel version.

Here's my code in creating custom menu and custom toolbar, I couldn't
find anything wrong with it, maybe some expert might be, it wouldbe
really helpful if you do.

=====================
Code in ThisWorkBook
=====================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveCustomMenu
Call Toolbar_OFF
End Sub

Private Sub Workbook_Open()
Call AddCustomMenu
Call Toolbar_ON
End Sub


=======================
Code in My Macro Module
=======================

Public Sub AddCustomMenu()
Call RemoveCustomMenu 'Remove custom menu if already exist
Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer
'
'Add custom menu to "Worksheet Menu Bar"
'
Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup,
Befo=iHelpIndex)
With muCustom
.Caption = "&Cus-Macro"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Run Format Current Report"
.FaceId = 550
.OnAction = "CusMacro.BOMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Reserve for Future Use"
.BeginGroup = True
.FaceId = 1296
.OnAction = ""
End With
End With
End Sub

Public Sub RemoveCustomMenu()
Dim cbWSMenuBar As CommandBar

'
'Remove Custom Menu from "Worksheet Menu Bar"
'
On Error Resume Next
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Cus-Macro").Delete

End Sub
Sub Toolbar_OFF()
Dim bar As CommandBar

' Delete the Commandbar if it already exists
For Each bar In Application.CommandBars
If bar.Name = cCommandBar Then bar.Delete
Next
End Sub

Sub Toolbar_ON()
Dim bar As CommandBar

Toolbar_OFF

Set bar = Application.CommandBars.Add(Name:=cCommandBar,
Position:=msoBarTop, Temporary:=True)

' Button
With bar.Controls.Add(Type:=msoControlButton)
.FaceId = 550
.Caption = "Cus-Macro"
.Style = msoButtonIconAndCaption
.OnAction = "CusMacro.BOMacro"
End With
CommandBars("Cus-Macro").Visible = True

End Sub


Thank you.

Hi Peter

If you click on the Excel X in the top bar by accident instead of the Workbook X to close a workbook,
the BeforeClose event of your Add-in run and delete all the menu items.

Maybe this is the problem


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Peter" wrote in message om...
Hi:

I have a wired problem with my Macro I generated, (running in Excel
2000)
This macro is copied to XLStart Folder. and I have this macro
installed on 3 user's computers. However on one of the user, my custom
menu and toolbar disappears from time-to-time:

This is how the process works:

There is an external web base software application user will run and
this software generates their reports in Excel format. So when user
runs reports for multiple accounts, it creates these reports and
loaded in Excel (same layout except different info on each excel file)

This application also has a custom menu, so WHEN USERS SWITCH TO THESE
EXCEL REPORTS, THEY WILL SEE THE CUSTOM MENU FROM THAT APPLICATION AS
WELL AS MY CUSTOM MENU AND TOOLBAR.

Only one user has a problem, NOT DURING THE INITIALLY, only after she
closes few excel reports, then at some point MY CUSTOM MENU AND
TOOLBAR DISAPPEARS TOO.

THE MOST WIRED PROBLEM IS THAT THIS IS NOT CONSISTENT, SOME DAY IT'S
OK AND SOME DAY IT'S NOT. AND THE OTHER 2 USERS HAD NEVER EXPERIENCE
THIS PROBLEM.

This is really wired and I did checked, all the users has the same
excel version.

Here's my code in creating custom menu and custom toolbar, I couldn't
find anything wrong with it, maybe some expert might be, it wouldbe
really helpful if you do.

=====================
Code in ThisWorkBook
=====================
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveCustomMenu
Call Toolbar_OFF
End Sub

Private Sub Workbook_Open()
Call AddCustomMenu
Call Toolbar_ON
End Sub


=======================
Code in My Macro Module
=======================

Public Sub AddCustomMenu()
Call RemoveCustomMenu 'Remove custom menu if already exist
Dim cbWSMenuBar As CommandBar
Dim muCustom As CommandBarControl
Dim iHelpIndex As Integer
'
'Add custom menu to "Worksheet Menu Bar"
'
Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
iHelpIndex = cbWSMenuBar.Controls("Help").Index
Set muCustom = cbWSMenuBar.Controls.Add(Type:=msoControlPopup,
Befo=iHelpIndex)
With muCustom
.Caption = "&Cus-Macro"
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Run Format Current Report"
.FaceId = 550
.OnAction = "CusMacro.BOMacro"
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Reserve for Future Use"
.BeginGroup = True
.FaceId = 1296
.OnAction = ""
End With
End With
End Sub

Public Sub RemoveCustomMenu()
Dim cbWSMenuBar As CommandBar

'
'Remove Custom Menu from "Worksheet Menu Bar"
'
On Error Resume Next
Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
cbWSMenuBar.Controls("Cus-Macro").Delete

End Sub
Sub Toolbar_OFF()
Dim bar As CommandBar

' Delete the Commandbar if it already exists
For Each bar In Application.CommandBars
If bar.Name = cCommandBar Then bar.Delete
Next
End Sub

Sub Toolbar_ON()
Dim bar As CommandBar

Toolbar_OFF

Set bar = Application.CommandBars.Add(Name:=cCommandBar,
Position:=msoBarTop, Temporary:=True)

' Button
With bar.Controls.Add(Type:=msoControlButton)
.FaceId = 550
.Caption = "Cus-Macro"
.Style = msoButtonIconAndCaption
.OnAction = "CusMacro.BOMacro"
End With
CommandBars("Cus-Macro").Visible = True

End Sub


Thank you.

Hi Ron:

Thank you for your prompt response, very much appreciated.
In terms of what you had suggested, I doubt that might be the cause.
Here's what I had done after first email, I added a call to
workbook_activate() method. see below...

============================
Private Sub Workbook_Open()
'Call the 'sub' so code is only modified in one place
Workbook_Activate
End Sub

'Workbook_activate is new,
Private Sub Workbook_Activate()
Call AddCustomMenu
Call Toolbar_ON
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call RemoveCustomMenu
Call Toolbar_OFF
End Sub
=================================

Since all the workbook is opened, and when user closes one of the
workbook, it closes and remove the menu, and that remove affect rest of
the open workbook and when user activates the other workbook, there is
no action in Workbook_activate() method before. Here I am adding it,
hopefully it will work when they actives the workbook.

Do you think this will fix the problem?

Thanks
Peter


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!