adding button to commandbars crahes Excel when started

[Guest]

I have a subroutine that is called from an Auto_Open subroutine that is shown
below. I have narrowed it down to this code as being the reason for excel
crashing on startup. Excel recoginizes that my Addin is the reason for the
problem and askes if I want to disable it. When that is done Excel starts
just fine. When I go to tools>addins.. and start my Addin up everything runs
fine, no errors are encountered. So why does this code create an error when
excel is starting as compared to after it has finished booting up? Thanks
for any help!! Here is my code...

Sub CreateRightClick()
Dim btn As CommandBarButton
Dim btn2 As CommandBarButton
Dim barArray As Variant
Dim i
barArray = Array("List Range Popup", "Cell", "Column", "Row")

For i = LBound(barArray) To UBound(barArray)
With Application.CommandBars(barArray(i))
.Reset
Set btn = .Controls.Add(Temporary:=True)
btn.BeginGroup = True
btn.Caption = "Apply Throughout"
btn.OnAction = "ApplyThroughout"
btn.FaceId = 201
Set btn2 = .Controls.Add(Temporary:=True)
btn2.Caption = "Calculate Cells"
btn2.OnAction = "ReCalcHighlighted"
btn2.FaceId = 202
End With
Set btn = Nothing
Set btn2 = Nothing
Next i
End Sub

 

[Jim Cone]

Well the first thing that comes to mind is the possibility that
the machine with the problem is not running Excel 2003.
If it is running XL 2003, then what happens if you stick an
"on error resume next" in the code?
That could possibly pinpoint the problem area for you, if a button or
something else is missing.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Brad Carman"
<[email protected].(donotspam)>
wrote in message
I have a subroutine that is called from an Auto_Open subroutine that is shown
below. I have narrowed it down to this code as being the reason for excel
crashing on startup. Excel recoginizes that my Addin is the reason for the
problem and askes if I want to disable it. When that is done Excel starts
just fine. When I go to tools>addins.. and start my Addin up everything runs
fine, no errors are encountered. So why does this code create an error when
excel is starting as compared to after it has finished booting up? Thanks
for any help!! Here is my code...

Sub CreateRightClick()
Dim btn As CommandBarButton
Dim btn2 As CommandBarButton
Dim barArray As Variant
Dim i
barArray = Array("List Range Popup", "Cell", "Column", "Row")

For i = LBound(barArray) To UBound(barArray)
With Application.CommandBars(barArray(i))
.Reset
Set btn = .Controls.Add(Temporary:=True)
btn.BeginGroup = True
btn.Caption = "Apply Throughout"
btn.OnAction = "ApplyThroughout"
btn.FaceId = 201
Set btn2 = .Controls.Add(Temporary:=True)
btn2.Caption = "Calculate Cells"
btn2.OnAction = "ReCalcHighlighted"
btn2.FaceId = 202
End With
Set btn = Nothing
Set btn2 = Nothing
Next i
End Sub

 

[Dave Peterson]

I've seen posts that say that excel can get "confused" while starting up. Some
pretty smart people have recommended this kind of thing.

Sub Auto_Open()
Application.OnTime Now, "Continue_Open"
End Sub

Then in a general module:

sub Continue_Open()
'your real code here
end sub

Maybe this'll avoid any "timing" issues that excel is having??????

Good luck.

 

[Peter T]

If Dave's suggestion works it's probably due to your (Brad) "Reset" of one
or more of the popup Commands, designed to work on a visible worksheet while
no such worksheet exists. The default buttons on these change according to
scenarios, egg the Insert.

Instead of resetting, particularly if you are distributing to users who may
not want their popups reset, consider attempting to delete your own buttons
under 'On Error Resume Next' then go on to add them. Alternatively attempt
to set a reference to them, if the ref succeeds re-apply your button
properties as you would had you just added the button.

Regards,
Peter T

 

[Guest]

Removing the reset command did the trick! Thanks everyone.

If Dave's suggestion works it's probably due to your (Brad) "Reset" of one
or more of the popup Commands, designed to work on a visible worksheet while
no such worksheet exists. The default buttons on these change according to
scenarios, egg the Insert.

Instead of resetting, particularly if you are distributing to users who may
not want their popups reset, consider attempting to delete your own buttons
under 'On Error Resume Next' then go on to add them. Alternatively attempt
to set a reference to them, if the ref succeeds re-apply your button
properties as you would had you just added the button.

Regards,
Peter T