workbook_open freezes Excel

B

Boycey

Windows 2000
Office XP

I have created an Excel add-in which creates a new menu item on
opening, and deletes it in closing. The menu works fine on my WinXP /
Office XP machine, and on a number of users' Win2k/Office Xp machines.

However, on certain computers, the add-in causes Excel to freeze on
opening. I have the following code in the "ThisWorkbook" section:

[START CODE]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenu

End Sub

Sub workbook_open()
NewMenu

End Sub
[END CODE]


which calls procedure "NewMenu" on open. "NewMenu" contains the
following code:

[START CODE]
Sub NewMenu()
Dim aMenu As Variant
Set aMenu = MenuBars(xlWorksheet).Menus.Add("IAD")

aMenu.MenuItems.Add "Format for MIDAS", OnAction:="multexformat"
aMenu.MenuItems.Add "Create labels", OnAction:="CreateLabels"
aMenu.MenuItems.Add "-"
aMenu.MenuItems.Add "Save rating change", OnAction:="pastevalues"
aMenu.MenuItems.Add "Format Holdings Report",
OnAction:="HoldingReport"

aMenu.MenuItems.Add "-"

aMenu.MenuItems.Add "About Add-in", OnAction:="AboutAddin"

aMenu.MenuItems("Save rating change").Enabled = False
aMenu.MenuItems("Format holdings report").Enabled = False

Select Case GetNetworkName
Case "tdonovan", "mnakai"
aMenu.MenuItems("Format holdings report").Enabled = True
Case "dboyce", "nharris"
aMenu.MenuItems("Format holdings report").Enabled = True
aMenu.MenuItems("Save rating change").Enabled = True
End Select
End Sub
[END CODE]

When I open Excel, the IAD menu appears, but Excel freezes, the menus
do not respond, and no "Book1" new file is created.

If I remove the NewMenu command from workbook_open, Excel opens as
normal. I can the run the workbook_open procedure from VBA with no
problem at all.

I cannot find anything wrong with the code - indeed, there can't be,
as it runs fine when Excel has already been opened. It is only when
it is run during the opening of Excel, on the Workbook_open event,
that problems occur.

This is a very frustrating problem, not least because the add-in works
fine on a number of other computers running exactly the same versions
of Windows and Office. If anybody has any suggestions, I would be
very grateful.

Thanks

Dom
 
D

Dave Peterson

I'd guess that it was a coincidence that excel was locking up, too.

I think I'd do the standard checks first.

A couple of guesses.

1. Clean up the windows temp folder. (then test it out)

2. sometimes the file that holds the customized toolbar settings gets hosed.
close excel
windows start button|Find (or search)
search for *.xlb
rename them to *.xlbOLD
restart excel

if it worked, delete the *.xlbOLD files and rebuild any customized toolbars.

If it didn't, rename them back to *.xlb.

Chip Pearson has some notes to help diagnose startup problems at:
http://www.cpearson.com/excel/StartupErrors.htm

But in xl97, menus were displaced with commandbars. Menus were still supported
for backward compatibility. Your code worked ok for me in xl2002, but you may
want to convert it to use commandbars.

This seemed to match what you wanted--I did put the whole thing in a General
module, though you could move things back to workbook_open and _beforeopen.

Option Explicit
Option Base 0
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub auto_close()
Call RemoveMenu
End Sub
Sub auto_open()
Call NewMenu
End Sub
Sub NewMenu()

Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myMacs As Variant
Dim myCaps As Variant
Dim iCtr As Variant
Dim OkToShow() As Boolean
Dim myBeginGroup() As Boolean

Call RemoveMenu

myMacs = Array("multexformat", _
"CreateLabels", _
"pastevalues", _
"HoldingReport", _
"AboutAddin")

myCaps = Array("Format for MIDAS", _
"Create labels", _
"Save rating change", _
"Format Holdings Report", _
"About Add-in")

ReDim OkToShow(LBound(myCaps) To UBound(myCaps))
OkToShow(0) = True
OkToShow(1) = True
OkToShow(2) = False
OkToShow(3) = False
OkToShow(4) = True

ReDim myBeginGroup(LBound(myCaps) To UBound(myCaps))
myBeginGroup(0) = False
myBeginGroup(1) = False
myBeginGroup(2) = True
myBeginGroup(3) = False
myBeginGroup(4) = True

Select Case LCase(GetNetworkName)
Case Is = "tdonovan", "mnakai"
OkToShow(3) = True
Case Is = "dboyce", "nharris"
OkToShow(2) = True
OkToShow(3) = True
End Select

If UBound(myMacs) <> UBound(myCaps) Then
MsgBox "Design error--not the same number of elements!"
Exit Sub
End If

With Application.CommandBars(1)
Set myCtrl = .Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, _
temporary:=True)

myCtrl.Caption = "IAD"

For iCtr = LBound(myCaps) To UBound(myCaps)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = ThisWorkbook.Name & "!" & myMacs(iCtr)
.Caption = myCaps(iCtr)
'.Enabled = OkToShow(iCtr)
.Visible = OkToShow(iCtr)
.BeginGroup = myBeginGroup(iCtr)
End With
Next iCtr
End With
End Sub
Sub RemoveMenu()
On Error Resume Next
Application.CommandBars(1).Controls("IAD").Delete
On Error GoTo 0
End Sub

Function GetNetworkName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
GetNetworkName = Left$(strUserName, lngLen - 1)
Else
GetNetworkName = ""
End If
End Function

Sub multexformat()
MsgBox "Multexformat"
End Sub
Sub CreateLabels()
MsgBox "createlabels"
End Sub
Sub pastevalues()
MsgBox "pastevalues"
End Sub
Sub HoldingReport()
MsgBox "holdingReport"
End Sub
Sub aboutaddin()
MsgBox "aboutaddin"
End Sub

When I have just a few macros that I want to add, I'd use a bunch of arrays.
But someday, when/if you want to add more, you may want to put lots of these
things in a worksheet and read from there.

John Walkenbach has a very neat workbook that does this. He calls his
MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm



Windows 2000
Office XP

I have created an Excel add-in which creates a new menu item on
opening, and deletes it in closing. The menu works fine on my WinXP /
Office XP machine, and on a number of users' Win2k/Office Xp machines.

However, on certain computers, the add-in causes Excel to freeze on
opening. I have the following code in the "ThisWorkbook" section:

[START CODE]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenu

End Sub

Sub workbook_open()
NewMenu

End Sub
[END CODE]

which calls procedure "NewMenu" on open. "NewMenu" contains the
following code:

[START CODE]
Sub NewMenu()
Dim aMenu As Variant
Set aMenu = MenuBars(xlWorksheet).Menus.Add("IAD")

aMenu.MenuItems.Add "Format for MIDAS", OnAction:="multexformat"
aMenu.MenuItems.Add "Create labels", OnAction:="CreateLabels"
aMenu.MenuItems.Add "-"
aMenu.MenuItems.Add "Save rating change", OnAction:="pastevalues"
aMenu.MenuItems.Add "Format Holdings Report",
OnAction:="HoldingReport"

aMenu.MenuItems.Add "-"

aMenu.MenuItems.Add "About Add-in", OnAction:="AboutAddin"

aMenu.MenuItems("Save rating change").Enabled = False
aMenu.MenuItems("Format holdings report").Enabled = False

Select Case GetNetworkName
Case "tdonovan", "mnakai"
aMenu.MenuItems("Format holdings report").Enabled = True
Case "dboyce", "nharris"
aMenu.MenuItems("Format holdings report").Enabled = True
aMenu.MenuItems("Save rating change").Enabled = True
End Select
End Sub
[END CODE]

When I open Excel, the IAD menu appears, but Excel freezes, the menus
do not respond, and no "Book1" new file is created.

If I remove the NewMenu command from workbook_open, Excel opens as
normal. I can the run the workbook_open procedure from VBA with no
problem at all.

I cannot find anything wrong with the code - indeed, there can't be,
as it runs fine when Excel has already been opened. It is only when
it is run during the opening of Excel, on the Workbook_open event,
that problems occur.

This is a very frustrating problem, not least because the add-in works
fine on a number of other computers running exactly the same versions
of Windows and Office. If anybody has any suggestions, I would be
very grateful.

Thanks

Dom
Click to expand...
 
B

Boycey

Hi Dave,

many thanks for your detailed response - I think I will use arrays
from now on to create menu bars! Unfortunately, the problem still
remains on the Win2k / Office XP computer.

It seems to be something to do with the Workbook_open event in Excel.
If Excel is started without the add-in, it opens fine. The add-in can
then be installed, and Excel will work fine, as will the add-in
functions. However, when Excel is restarted, the program freezes.

I have checked the links you gave me, but the solutions all seem to
stop at finding the add-in causing the problem. I know this already,
and need to determine why the add-in freezes Excel.

Any more help from anyone would be greatly appreciated.

Dom



Dave Peterson said:
I'd guess that it was a coincidence that excel was locking up, too.

I think I'd do the standard checks first.

A couple of guesses.

1. Clean up the windows temp folder. (then test it out)

2. sometimes the file that holds the customized toolbar settings gets hosed.
close excel
windows start button|Find (or search)
search for *.xlb
rename them to *.xlbOLD
restart excel

if it worked, delete the *.xlbOLD files and rebuild any customized toolbars.

If it didn't, rename them back to *.xlb.

Chip Pearson has some notes to help diagnose startup problems at:
http://www.cpearson.com/excel/StartupErrors.htm

But in xl97, menus were displaced with commandbars. Menus were still supported
for backward compatibility. Your code worked ok for me in xl2002, but you may
want to convert it to use commandbars.

This seemed to match what you wanted--I did put the whole thing in a General
module, though you could move things back to workbook_open and _beforeopen.

Option Explicit
Option Base 0
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub auto_close()
Call RemoveMenu
End Sub
Sub auto_open()
Call NewMenu
End Sub
Sub NewMenu()

Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myMacs As Variant
Dim myCaps As Variant
Dim iCtr As Variant
Dim OkToShow() As Boolean
Dim myBeginGroup() As Boolean

Call RemoveMenu

myMacs = Array("multexformat", _
"CreateLabels", _
"pastevalues", _
"HoldingReport", _
"AboutAddin")

myCaps = Array("Format for MIDAS", _
"Create labels", _
"Save rating change", _
"Format Holdings Report", _
"About Add-in")

ReDim OkToShow(LBound(myCaps) To UBound(myCaps))
OkToShow(0) = True
OkToShow(1) = True
OkToShow(2) = False
OkToShow(3) = False
OkToShow(4) = True

ReDim myBeginGroup(LBound(myCaps) To UBound(myCaps))
myBeginGroup(0) = False
myBeginGroup(1) = False
myBeginGroup(2) = True
myBeginGroup(3) = False
myBeginGroup(4) = True

Select Case LCase(GetNetworkName)
Case Is = "tdonovan", "mnakai"
OkToShow(3) = True
Case Is = "dboyce", "nharris"
OkToShow(2) = True
OkToShow(3) = True
End Select

If UBound(myMacs) <> UBound(myCaps) Then
MsgBox "Design error--not the same number of elements!"
Exit Sub
End If

With Application.CommandBars(1)
Set myCtrl = .Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, _
temporary:=True)

myCtrl.Caption = "IAD"

For iCtr = LBound(myCaps) To UBound(myCaps)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = ThisWorkbook.Name & "!" & myMacs(iCtr)
.Caption = myCaps(iCtr)
'.Enabled = OkToShow(iCtr)
.Visible = OkToShow(iCtr)
.BeginGroup = myBeginGroup(iCtr)
End With
Next iCtr
End With
End Sub
Sub RemoveMenu()
On Error Resume Next
Application.CommandBars(1).Controls("IAD").Delete
On Error GoTo 0
End Sub

Function GetNetworkName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
GetNetworkName = Left$(strUserName, lngLen - 1)
Else
GetNetworkName = ""
End If
End Function

Sub multexformat()
MsgBox "Multexformat"
End Sub
Sub CreateLabels()
MsgBox "createlabels"
End Sub
Sub pastevalues()
MsgBox "pastevalues"
End Sub
Sub HoldingReport()
MsgBox "holdingReport"
End Sub
Sub aboutaddin()
MsgBox "aboutaddin"
End Sub

When I have just a few macros that I want to add, I'd use a bunch of arrays.
But someday, when/if you want to add more, you may want to put lots of these
things in a worksheet and read from there.

John Walkenbach has a very neat workbook that does this. He calls his
MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm



Windows 2000
Office XP

I have created an Excel add-in which creates a new menu item on
opening, and deletes it in closing. The menu works fine on my WinXP /
Office XP machine, and on a number of users' Win2k/Office Xp machines.

However, on certain computers, the add-in causes Excel to freeze on
opening. I have the following code in the "ThisWorkbook" section:

[START CODE]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenu

End Sub

Sub workbook_open()
NewMenu

End Sub
[END CODE]

which calls procedure "NewMenu" on open. "NewMenu" contains the
following code:

[START CODE]
Sub NewMenu()
Dim aMenu As Variant
Set aMenu = MenuBars(xlWorksheet).Menus.Add("IAD")

aMenu.MenuItems.Add "Format for MIDAS", OnAction:="multexformat"
aMenu.MenuItems.Add "Create labels", OnAction:="CreateLabels"
aMenu.MenuItems.Add "-"
aMenu.MenuItems.Add "Save rating change", OnAction:="pastevalues"
aMenu.MenuItems.Add "Format Holdings Report",
OnAction:="HoldingReport"

aMenu.MenuItems.Add "-"

aMenu.MenuItems.Add "About Add-in", OnAction:="AboutAddin"

aMenu.MenuItems("Save rating change").Enabled = False
aMenu.MenuItems("Format holdings report").Enabled = False

Select Case GetNetworkName
Case "tdonovan", "mnakai"
aMenu.MenuItems("Format holdings report").Enabled = True
Case "dboyce", "nharris"
aMenu.MenuItems("Format holdings report").Enabled = True
aMenu.MenuItems("Save rating change").Enabled = True
End Select
End Sub
[END CODE]

When I open Excel, the IAD menu appears, but Excel freezes, the menus
do not respond, and no "Book1" new file is created.

If I remove the NewMenu command from workbook_open, Excel opens as
normal. I can the run the workbook_open procedure from VBA with no
problem at all.

I cannot find anything wrong with the code - indeed, there can't be,
as it runs fine when Excel has already been opened. It is only when
it is run during the opening of Excel, on the Workbook_open event,
that problems occur.

This is a very frustrating problem, not least because the add-in works
fine on a number of other computers running exactly the same versions
of Windows and Office. If anybody has any suggestions, I would be
very grateful.

Thanks

Dom
Click to expand...
Click to expand...
 
D

Dave Peterson

I never used those type of menus--so I don't really know if they can cause
problems like you're having (but I would guess not--else there would be lots of
questions about how to convert these to commandbars.)

Did you try converting to commandbars and still have the trouble?

And when inexplicable things happen, this is a common suggestion:

Try running Rob Bovey's code cleaner program.

You can find it at:
http://www.appspro.com/
Hi Dave,

many thanks for your detailed response - I think I will use arrays
from now on to create menu bars! Unfortunately, the problem still
remains on the Win2k / Office XP computer.

It seems to be something to do with the Workbook_open event in Excel.
If Excel is started without the add-in, it opens fine. The add-in can
then be installed, and Excel will work fine, as will the add-in
functions. However, when Excel is restarted, the program freezes.

I have checked the links you gave me, but the solutions all seem to
stop at finding the add-in causing the problem. I know this already,
and need to determine why the add-in freezes Excel.

Any more help from anyone would be greatly appreciated.

Dom

Dave Peterson said:
I'd guess that it was a coincidence that excel was locking up, too.

I think I'd do the standard checks first.

A couple of guesses.

1. Clean up the windows temp folder. (then test it out)

2. sometimes the file that holds the customized toolbar settings gets hosed.
close excel
windows start button|Find (or search)
search for *.xlb
rename them to *.xlbOLD
restart excel

if it worked, delete the *.xlbOLD files and rebuild any customized toolbars.

If it didn't, rename them back to *.xlb.

Chip Pearson has some notes to help diagnose startup problems at:
http://www.cpearson.com/excel/StartupErrors.htm

But in xl97, menus were displaced with commandbars. Menus were still supported
for backward compatibility. Your code worked ok for me in xl2002, but you may
want to convert it to use commandbars.

This seemed to match what you wanted--I did put the whole thing in a General
module, though you could move things back to workbook_open and _beforeopen.

Option Explicit
Option Base 0
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub auto_close()
Call RemoveMenu
End Sub
Sub auto_open()
Call NewMenu
End Sub
Sub NewMenu()

Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myMacs As Variant
Dim myCaps As Variant
Dim iCtr As Variant
Dim OkToShow() As Boolean
Dim myBeginGroup() As Boolean

Call RemoveMenu

myMacs = Array("multexformat", _
"CreateLabels", _
"pastevalues", _
"HoldingReport", _
"AboutAddin")

myCaps = Array("Format for MIDAS", _
"Create labels", _
"Save rating change", _
"Format Holdings Report", _
"About Add-in")

ReDim OkToShow(LBound(myCaps) To UBound(myCaps))
OkToShow(0) = True
OkToShow(1) = True
OkToShow(2) = False
OkToShow(3) = False
OkToShow(4) = True

ReDim myBeginGroup(LBound(myCaps) To UBound(myCaps))
myBeginGroup(0) = False
myBeginGroup(1) = False
myBeginGroup(2) = True
myBeginGroup(3) = False
myBeginGroup(4) = True

Select Case LCase(GetNetworkName)
Case Is = "tdonovan", "mnakai"
OkToShow(3) = True
Case Is = "dboyce", "nharris"
OkToShow(2) = True
OkToShow(3) = True
End Select

If UBound(myMacs) <> UBound(myCaps) Then
MsgBox "Design error--not the same number of elements!"
Exit Sub
End If

With Application.CommandBars(1)
Set myCtrl = .Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, _
temporary:=True)

myCtrl.Caption = "IAD"

For iCtr = LBound(myCaps) To UBound(myCaps)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = ThisWorkbook.Name & "!" & myMacs(iCtr)
.Caption = myCaps(iCtr)
'.Enabled = OkToShow(iCtr)
.Visible = OkToShow(iCtr)
.BeginGroup = myBeginGroup(iCtr)
End With
Next iCtr
End With
End Sub
Sub RemoveMenu()
On Error Resume Next
Application.CommandBars(1).Controls("IAD").Delete
On Error GoTo 0
End Sub

Function GetNetworkName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
GetNetworkName = Left$(strUserName, lngLen - 1)
Else
GetNetworkName = ""
End If
End Function

Sub multexformat()
MsgBox "Multexformat"
End Sub
Sub CreateLabels()
MsgBox "createlabels"
End Sub
Sub pastevalues()
MsgBox "pastevalues"
End Sub
Sub HoldingReport()
MsgBox "holdingReport"
End Sub
Sub aboutaddin()
MsgBox "aboutaddin"
End Sub

When I have just a few macros that I want to add, I'd use a bunch of arrays.
But someday, when/if you want to add more, you may want to put lots of these
things in a worksheet and read from there.

John Walkenbach has a very neat workbook that does this. He calls his
MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm



Windows 2000
Office XP

I have created an Excel add-in which creates a new menu item on
opening, and deletes it in closing. The menu works fine on my WinXP /
Office XP machine, and on a number of users' Win2k/Office Xp machines.

However, on certain computers, the add-in causes Excel to freeze on
opening. I have the following code in the "ThisWorkbook" section:

[START CODE]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenu

End Sub

Sub workbook_open()
NewMenu

End Sub
[END CODE]

which calls procedure "NewMenu" on open. "NewMenu" contains the
following code:

[START CODE]
Sub NewMenu()
Dim aMenu As Variant
Set aMenu = MenuBars(xlWorksheet).Menus.Add("IAD")

aMenu.MenuItems.Add "Format for MIDAS", OnAction:="multexformat"
aMenu.MenuItems.Add "Create labels", OnAction:="CreateLabels"
aMenu.MenuItems.Add "-"
aMenu.MenuItems.Add "Save rating change", OnAction:="pastevalues"
aMenu.MenuItems.Add "Format Holdings Report",
OnAction:="HoldingReport"

aMenu.MenuItems.Add "-"

aMenu.MenuItems.Add "About Add-in", OnAction:="AboutAddin"

aMenu.MenuItems("Save rating change").Enabled = False
aMenu.MenuItems("Format holdings report").Enabled = False

Select Case GetNetworkName
Case "tdonovan", "mnakai"
aMenu.MenuItems("Format holdings report").Enabled = True
Case "dboyce", "nharris"
aMenu.MenuItems("Format holdings report").Enabled = True
aMenu.MenuItems("Save rating change").Enabled = True
End Select
End Sub
[END CODE]

When I open Excel, the IAD menu appears, but Excel freezes, the menus
do not respond, and no "Book1" new file is created.

If I remove the NewMenu command from workbook_open, Excel opens as
normal. I can the run the workbook_open procedure from VBA with no
problem at all.

I cannot find anything wrong with the code - indeed, there can't be,
as it runs fine when Excel has already been opened. It is only when
it is run during the opening of Excel, on the Workbook_open event,
that problems occur.

This is a very frustrating problem, not least because the add-in works
fine on a number of other computers running exactly the same versions
of Windows and Office. If anybody has any suggestions, I would be
very grateful.

Thanks

Dom
Click to expand...
Click to expand...
Click to expand...
 
B

Boycey

Dave,

thanks again for your help. For some inexplicable reason, the add-in
now works fine. I had even gone to the extent of reinstalling Office,
but to no avail. Then, yesterday morning, the user started excel and
opened the add-in manually, a necessary workaround while the auto_open
problem was investigated. When he closed Excel down and restarted,
with the add-in enabled, Excel opened fine, without freezing as it had
done in the past. Many restarts later, and the problem seems to have
disappeared.

Not the most satisfactory of solutions, as the cause will never be
discovered, but at leas t it works now!

and thanks for the link to the code cleaner - looks very interesting.

Regards

Dom

Dave Peterson said:
I never used those type of menus--so I don't really know if they can cause
problems like you're having (but I would guess not--else there would be lots of
questions about how to convert these to commandbars.)

Did you try converting to commandbars and still have the trouble?

And when inexplicable things happen, this is a common suggestion:

Try running Rob Bovey's code cleaner program.

You can find it at:
http://www.appspro.com/
Hi Dave,

many thanks for your detailed response - I think I will use arrays
from now on to create menu bars! Unfortunately, the problem still
remains on the Win2k / Office XP computer.

It seems to be something to do with the Workbook_open event in Excel.
If Excel is started without the add-in, it opens fine. The add-in can
then be installed, and Excel will work fine, as will the add-in
functions. However, when Excel is restarted, the program freezes.

I have checked the links you gave me, but the solutions all seem to
stop at finding the add-in causing the problem. I know this already,
and need to determine why the add-in freezes Excel.

Any more help from anyone would be greatly appreciated.

Dom

Dave Peterson said:
I'd guess that it was a coincidence that excel was locking up, too.

I think I'd do the standard checks first.

A couple of guesses.

1. Clean up the windows temp folder. (then test it out)

2. sometimes the file that holds the customized toolbar settings gets hosed.
close excel
windows start button|Find (or search)
search for *.xlb
rename them to *.xlbOLD
restart excel

if it worked, delete the *.xlbOLD files and rebuild any customized toolbars.

If it didn't, rename them back to *.xlb.

Chip Pearson has some notes to help diagnose startup problems at:
http://www.cpearson.com/excel/StartupErrors.htm

But in xl97, menus were displaced with commandbars. Menus were still supported
for backward compatibility. Your code worked ok for me in xl2002, but you may
want to convert it to use commandbars.

This seemed to match what you wanted--I did put the whole thing in a General
module, though you could move things back to workbook_open and _beforeopen.

Option Explicit
Option Base 0
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub auto_close()
Call RemoveMenu
End Sub
Sub auto_open()
Call NewMenu
End Sub
Sub NewMenu()

Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myMacs As Variant
Dim myCaps As Variant
Dim iCtr As Variant
Dim OkToShow() As Boolean
Dim myBeginGroup() As Boolean

Call RemoveMenu

myMacs = Array("multexformat", _
"CreateLabels", _
"pastevalues", _
"HoldingReport", _
"AboutAddin")

myCaps = Array("Format for MIDAS", _
"Create labels", _
"Save rating change", _
"Format Holdings Report", _
"About Add-in")

ReDim OkToShow(LBound(myCaps) To UBound(myCaps))
OkToShow(0) = True
OkToShow(1) = True
OkToShow(2) = False
OkToShow(3) = False
OkToShow(4) = True

ReDim myBeginGroup(LBound(myCaps) To UBound(myCaps))
myBeginGroup(0) = False
myBeginGroup(1) = False
myBeginGroup(2) = True
myBeginGroup(3) = False
myBeginGroup(4) = True

Select Case LCase(GetNetworkName)
Case Is = "tdonovan", "mnakai"
OkToShow(3) = True
Case Is = "dboyce", "nharris"
OkToShow(2) = True
OkToShow(3) = True
End Select

If UBound(myMacs) <> UBound(myCaps) Then
MsgBox "Design error--not the same number of elements!"
Exit Sub
End If

With Application.CommandBars(1)
Set myCtrl = .Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, _
temporary:=True)

myCtrl.Caption = "IAD"

For iCtr = LBound(myCaps) To UBound(myCaps)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = ThisWorkbook.Name & "!" & myMacs(iCtr)
.Caption = myCaps(iCtr)
'.Enabled = OkToShow(iCtr)
.Visible = OkToShow(iCtr)
.BeginGroup = myBeginGroup(iCtr)
End With
Next iCtr
End With
End Sub
Sub RemoveMenu()
On Error Resume Next
Application.CommandBars(1).Controls("IAD").Delete
On Error GoTo 0
End Sub

Function GetNetworkName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
GetNetworkName = Left$(strUserName, lngLen - 1)
Else
GetNetworkName = ""
End If
End Function

Sub multexformat()
MsgBox "Multexformat"
End Sub
Sub CreateLabels()
MsgBox "createlabels"
End Sub
Sub pastevalues()
MsgBox "pastevalues"
End Sub
Sub HoldingReport()
MsgBox "holdingReport"
End Sub
Sub aboutaddin()
MsgBox "aboutaddin"
End Sub

When I have just a few macros that I want to add, I'd use a bunch of arrays.
But someday, when/if you want to add more, you may want to put lots of these
things in a worksheet and read from there.

John Walkenbach has a very neat workbook that does this. He calls his
MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm




Boycey wrote:

Windows 2000
Office XP

I have created an Excel add-in which creates a new menu item on
opening, and deletes it in closing. The menu works fine on my WinXP /
Office XP machine, and on a number of users' Win2k/Office Xp machines.

However, on certain computers, the add-in causes Excel to freeze on
opening. I have the following code in the "ThisWorkbook" section:

[START CODE]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenu

End Sub

Sub workbook_open()
NewMenu

End Sub
[END CODE]

which calls procedure "NewMenu" on open. "NewMenu" contains the
following code:

[START CODE]
Sub NewMenu()
Dim aMenu As Variant
Set aMenu = MenuBars(xlWorksheet).Menus.Add("IAD")

aMenu.MenuItems.Add "Format for MIDAS", OnAction:="multexformat"
aMenu.MenuItems.Add "Create labels", OnAction:="CreateLabels"
aMenu.MenuItems.Add "-"
aMenu.MenuItems.Add "Save rating change", OnAction:="pastevalues"
aMenu.MenuItems.Add "Format Holdings Report",
OnAction:="HoldingReport"

aMenu.MenuItems.Add "-"

aMenu.MenuItems.Add "About Add-in", OnAction:="AboutAddin"

aMenu.MenuItems("Save rating change").Enabled = False
aMenu.MenuItems("Format holdings report").Enabled = False

Select Case GetNetworkName
Case "tdonovan", "mnakai"
aMenu.MenuItems("Format holdings report").Enabled = True
Case "dboyce", "nharris"
aMenu.MenuItems("Format holdings report").Enabled = True
aMenu.MenuItems("Save rating change").Enabled = True
End Select
End Sub
[END CODE]

When I open Excel, the IAD menu appears, but Excel freezes, the menus
do not respond, and no "Book1" new file is created.

If I remove the NewMenu command from workbook_open, Excel opens as
normal. I can the run the workbook_open procedure from VBA with no
problem at all.

I cannot find anything wrong with the code - indeed, there can't be,
as it runs fine when Excel has already been opened. It is only when
it is run during the opening of Excel, on the Workbook_open event,
that problems occur.

This is a very frustrating problem, not least because the add-in works
fine on a number of other computers running exactly the same versions
of Windows and Office. If anybody has any suggestions, I would be
very grateful.

Thanks

Dom
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Call it a victory and run for your life when that user shows up again!
Dave,

thanks again for your help. For some inexplicable reason, the add-in
now works fine. I had even gone to the extent of reinstalling Office,
but to no avail. Then, yesterday morning, the user started excel and
opened the add-in manually, a necessary workaround while the auto_open
problem was investigated. When he closed Excel down and restarted,
with the add-in enabled, Excel opened fine, without freezing as it had
done in the past. Many restarts later, and the problem seems to have
disappeared.

Not the most satisfactory of solutions, as the cause will never be
discovered, but at leas t it works now!

and thanks for the link to the code cleaner - looks very interesting.

Regards

Dom

Dave Peterson said:
I never used those type of menus--so I don't really know if they can cause
problems like you're having (but I would guess not--else there would be lots of
questions about how to convert these to commandbars.)

Did you try converting to commandbars and still have the trouble?

And when inexplicable things happen, this is a common suggestion:

Try running Rob Bovey's code cleaner program.

You can find it at:
http://www.appspro.com/
Hi Dave,

many thanks for your detailed response - I think I will use arrays
from now on to create menu bars! Unfortunately, the problem still
remains on the Win2k / Office XP computer.

It seems to be something to do with the Workbook_open event in Excel.
If Excel is started without the add-in, it opens fine. The add-in can
then be installed, and Excel will work fine, as will the add-in
functions. However, when Excel is restarted, the program freezes.

I have checked the links you gave me, but the solutions all seem to
stop at finding the add-in causing the problem. I know this already,
and need to determine why the add-in freezes Excel.

Any more help from anyone would be greatly appreciated.

Dom

I'd guess that it was a coincidence that excel was locking up, too.

I think I'd do the standard checks first.

A couple of guesses.

1. Clean up the windows temp folder. (then test it out)

2. sometimes the file that holds the customized toolbar settings gets hosed.
close excel
windows start button|Find (or search)
search for *.xlb
rename them to *.xlbOLD
restart excel

if it worked, delete the *.xlbOLD files and rebuild any customized toolbars.

If it didn't, rename them back to *.xlb.

Chip Pearson has some notes to help diagnose startup problems at:
http://www.cpearson.com/excel/StartupErrors.htm

But in xl97, menus were displaced with commandbars. Menus were still supported
for backward compatibility. Your code worked ok for me in xl2002, but you may
want to convert it to use commandbars.

This seemed to match what you wanted--I did put the whole thing in a General
module, though you could move things back to workbook_open and _beforeopen.

Option Explicit
Option Base 0
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub auto_close()
Call RemoveMenu
End Sub
Sub auto_open()
Call NewMenu
End Sub
Sub NewMenu()

Dim myCtrl As CommandBarControl
Dim myBTN As CommandBarButton
Dim myMacs As Variant
Dim myCaps As Variant
Dim iCtr As Variant
Dim OkToShow() As Boolean
Dim myBeginGroup() As Boolean

Call RemoveMenu

myMacs = Array("multexformat", _
"CreateLabels", _
"pastevalues", _
"HoldingReport", _
"AboutAddin")

myCaps = Array("Format for MIDAS", _
"Create labels", _
"Save rating change", _
"Format Holdings Report", _
"About Add-in")

ReDim OkToShow(LBound(myCaps) To UBound(myCaps))
OkToShow(0) = True
OkToShow(1) = True
OkToShow(2) = False
OkToShow(3) = False
OkToShow(4) = True

ReDim myBeginGroup(LBound(myCaps) To UBound(myCaps))
myBeginGroup(0) = False
myBeginGroup(1) = False
myBeginGroup(2) = True
myBeginGroup(3) = False
myBeginGroup(4) = True

Select Case LCase(GetNetworkName)
Case Is = "tdonovan", "mnakai"
OkToShow(3) = True
Case Is = "dboyce", "nharris"
OkToShow(2) = True
OkToShow(3) = True
End Select

If UBound(myMacs) <> UBound(myCaps) Then
MsgBox "Design error--not the same number of elements!"
Exit Sub
End If

With Application.CommandBars(1)
Set myCtrl = .Controls.Add(Type:=msoControlPopup, _
before:=.Controls.Count, _
temporary:=True)

myCtrl.Caption = "IAD"

For iCtr = LBound(myCaps) To UBound(myCaps)
Set myBTN = myCtrl.Controls.Add(Type:=msoControlButton, _
temporary:=True)
With myBTN
.OnAction = ThisWorkbook.Name & "!" & myMacs(iCtr)
.Caption = myCaps(iCtr)
'.Enabled = OkToShow(iCtr)
.Visible = OkToShow(iCtr)
.BeginGroup = myBeginGroup(iCtr)
End With
Next iCtr
End With
End Sub
Sub RemoveMenu()
On Error Resume Next
Application.CommandBars(1).Controls("IAD").Delete
On Error GoTo 0
End Sub

Function GetNetworkName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
GetNetworkName = Left$(strUserName, lngLen - 1)
Else
GetNetworkName = ""
End If
End Function

Sub multexformat()
MsgBox "Multexformat"
End Sub
Sub CreateLabels()
MsgBox "createlabels"
End Sub
Sub pastevalues()
MsgBox "pastevalues"
End Sub
Sub HoldingReport()
MsgBox "holdingReport"
End Sub
Sub aboutaddin()
MsgBox "aboutaddin"
End Sub

When I have just a few macros that I want to add, I'd use a bunch of arrays.
But someday, when/if you want to add more, you may want to put lots of these
things in a worksheet and read from there.

John Walkenbach has a very neat workbook that does this. He calls his
MenuMaker.xls.

You can find it:
http://j-walk.com/ss/excel/tips/tip53.htm




Boycey wrote:

Windows 2000
Office XP

I have created an Excel add-in which creates a new menu item on
opening, and deletes it in closing. The menu works fine on my WinXP /
Office XP machine, and on a number of users' Win2k/Office Xp machines.

However, on certain computers, the add-in causes Excel to freeze on
opening. I have the following code in the "ThisWorkbook" section:

[START CODE]
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
RemoveMenu

End Sub

Sub workbook_open()
NewMenu

End Sub
[END CODE]

which calls procedure "NewMenu" on open. "NewMenu" contains the
following code:

[START CODE]
Sub NewMenu()
Dim aMenu As Variant
Set aMenu = MenuBars(xlWorksheet).Menus.Add("IAD")

aMenu.MenuItems.Add "Format for MIDAS", OnAction:="multexformat"
aMenu.MenuItems.Add "Create labels", OnAction:="CreateLabels"
aMenu.MenuItems.Add "-"
aMenu.MenuItems.Add "Save rating change", OnAction:="pastevalues"
aMenu.MenuItems.Add "Format Holdings Report",
OnAction:="HoldingReport"

aMenu.MenuItems.Add "-"

aMenu.MenuItems.Add "About Add-in", OnAction:="AboutAddin"

aMenu.MenuItems("Save rating change").Enabled = False
aMenu.MenuItems("Format holdings report").Enabled = False

Select Case GetNetworkName
Case "tdonovan", "mnakai"
aMenu.MenuItems("Format holdings report").Enabled = True
Case "dboyce", "nharris"
aMenu.MenuItems("Format holdings report").Enabled = True
aMenu.MenuItems("Save rating change").Enabled = True
End Select
End Sub
[END CODE]

When I open Excel, the IAD menu appears, but Excel freezes, the menus
do not respond, and no "Book1" new file is created.

If I remove the NewMenu command from workbook_open, Excel opens as
normal. I can the run the workbook_open procedure from VBA with no
problem at all.

I cannot find anything wrong with the code - indeed, there can't be,
as it runs fine when Excel has already been opened. It is only when
it is run during the opening of Excel, on the Workbook_open event,
that problems occur.

This is a very frustrating problem, not least because the add-in works
fine on a number of other computers running exactly the same versions
of Windows and Office. If anybody has any suggestions, I would be
very grateful.

Thanks

Dom
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Excel 2007 with Excel 2003 template 1
onAction events on NewMenu 6
Private Sub Workbook_Open() & Workbook_BeforeClose(cancel As Boolean) 4
Help reqd with adding menu items!! 4
Workbook_Open CommandBar corrupt excel files 1
Custom Toolbar Loading issue 2
Workbook_Open event fails to fire 3
Open workbook without running Workbook_Open Event 4

Top