I created a spreadsheet that has auto_open, workbook_Open, and window_activate event subroutines. When I open from Excel, it all works fine. When I open via a link in my e-mail (Which happens to be Novell Groupwise) it opens the spreadsheet within internet explorer, I get Automation Error 440. The line that fails is:
With Application.CommandBars("cells")
Set temp = .Controls.Add(msoControlButton)
It appears that my subroutine cannot find the "cells" command bar (this is the built in shortcut menu obtained by right-clicking a cell). Simply upgrading my version of ie or changing to Netscape won't help because I can't upgrade all of the other users.
I have tried:
1) Adding a 10 second wait in workbook_Open, and window_activate event subroutines. It waits, but then continues as before.
2) Commenting out the auto_open sub. No change.
3) Remove sub calls from workbook open to another sub and then run manually after opening. Open fine, but fails the same when I manually run the macro.
None worked. Yet if I right-click the spreadsheet, the "cells" menu does appear.
After ending the first error, when I perform an action that trys to enable/disable the control that was added to the shortcut bar, then I get an error. When I "end" that error, excel crashes with a fatal exception error.
I am at a loss. For now, my work-around is that I'll send a link to the directory and have people open the file from the directory.
Any suggestions?
REF ====================================
Private clcmd As Long
'Sub auto_open()
'clcmd = Application.Calculation
'Application.Calculation = xlManual
'End Sub
Private Sub Workbook_Open()
'2002-05-03: Created by Brian West
' Display a message box dialog when the workbook opens.
WaitForXsecs (10)
msgbox (...)
{snip ...}
End sub
Sub WaitForXsecs(SecondsToWait)
' 2002-05-06: Created by Brian West
' From MS Help
' This example pauses a running macro for approximately 10 seconds.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + SecondsToWait
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub
Private Sub Workbook_Activate()
' 2002-05-04: Created by Brian West
' Recalc the sheet when selected,
' otherwise sometimes all of the rows appear blue,
' even when in automatica calculation.
BasicReCalcNow
CustomiseShortcutMenu ("Cell") '
CustomiseShortcutMenu ("Row")
DisableShortcutMenu ("Cell")
DisableShortcutMenu ("Row")
End Sub
Sub CustomiseShortcutMenu(TargetMenu As String)
'2002-04-26: Created by Brian West
'From MrExcel Messageboard - Works!
With Application.CommandBars(TargetMenu)
Set temp = .Controls.Add(msoControlButton) '<------FAILS HERE AS IF COMMANDBAR "CELLS" DOES NOT EXIST.
temp.Caption = "Insert Spec Rows"
temp.OnAction = "InsertRowCopyAutoNumCells"
temp.BeginGroup = True
End With
{SNIP}
EndSub
With Application.CommandBars("cells")
Set temp = .Controls.Add(msoControlButton)
It appears that my subroutine cannot find the "cells" command bar (this is the built in shortcut menu obtained by right-clicking a cell). Simply upgrading my version of ie or changing to Netscape won't help because I can't upgrade all of the other users.
I have tried:
1) Adding a 10 second wait in workbook_Open, and window_activate event subroutines. It waits, but then continues as before.
2) Commenting out the auto_open sub. No change.
3) Remove sub calls from workbook open to another sub and then run manually after opening. Open fine, but fails the same when I manually run the macro.
None worked. Yet if I right-click the spreadsheet, the "cells" menu does appear.
After ending the first error, when I perform an action that trys to enable/disable the control that was added to the shortcut bar, then I get an error. When I "end" that error, excel crashes with a fatal exception error.
I am at a loss. For now, my work-around is that I'll send a link to the directory and have people open the file from the directory.
Any suggestions?
REF ====================================
Private clcmd As Long
'Sub auto_open()
'clcmd = Application.Calculation
'Application.Calculation = xlManual
'End Sub
Private Sub Workbook_Open()
'2002-05-03: Created by Brian West
' Display a message box dialog when the workbook opens.
WaitForXsecs (10)
msgbox (...)
{snip ...}
End sub
Sub WaitForXsecs(SecondsToWait)
' 2002-05-06: Created by Brian West
' From MS Help
' This example pauses a running macro for approximately 10 seconds.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + SecondsToWait
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub
Private Sub Workbook_Activate()
' 2002-05-04: Created by Brian West
' Recalc the sheet when selected,
' otherwise sometimes all of the rows appear blue,
' even when in automatica calculation.
BasicReCalcNow
CustomiseShortcutMenu ("Cell") '
CustomiseShortcutMenu ("Row")
DisableShortcutMenu ("Cell")
DisableShortcutMenu ("Row")
End Sub
Sub CustomiseShortcutMenu(TargetMenu As String)
'2002-04-26: Created by Brian West
'From MrExcel Messageboard - Works!
With Application.CommandBars(TargetMenu)
Set temp = .Controls.Add(msoControlButton) '<------FAILS HERE AS IF COMMANDBAR "CELLS" DOES NOT EXIST.
temp.Caption = "Insert Spec Rows"
temp.OnAction = "InsertRowCopyAutoNumCells"
temp.BeginGroup = True
End With
{SNIP}
EndSub