Error when Solver Reference Enabled

B

BU_Paul

The following error only happens when I start a new instance of excel
and will not re-occur if I close the workbook and re-open it.

When I open a particular application that has the solver reference
enabled, I get the following error:
Macro Error at Cell:
[Solver.xla]!Excel4Functions!A31

If I pick continue, there are no problems and all my macros work as
expected. If I pick step, I get Formula: =End.If() then I pick step
into and get Formula: =return(). I pick step into again and it
closes. And it still works fine after that.

This error occurs after the auto_open macro runs. Here is the macro:
Sub Auto_Open()
'
' Auto_Op Macro
' Macro recorded 3/23/2004
'

'
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
End With
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Worksheet Menu Bar").Controls(10).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(9).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(7).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(6).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(5).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(4).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(3).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(2).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(1).Delete
ActiveSheet.Unprotect "prs"
Rows("28:42").Select
Selection.EntireRow.Hidden = False
Range("I24").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("I25:O42").Select
Selection.Sort Key1:=Range("O25"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Rows("28:42").Select
Selection.EntireRow.Hidden = True
Range("I7").Select
ActiveSheet.Protect "prs", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

Any suggestions? Thanks.
 
B

BU_Paul

I noticed that a solution to other problems with solver was to restore
solver.xla to a version prior to SP3 (Office 2000). I am running
Office XP but used the upgrade disk to install. I assume that the
solver.xla file that was still being used after upgrade was the one
that came with Office 2000 SP3. So, I found the solver.xla file from
prior to SP3 (I think) and replaced it. I still had the problem. I
then used a trial version of solver.xla from Frontline Systems and the
problem is resolved. Unfortunately, this is not a fix but provides
some additional clues. Perhaps, I should make sure I have a version
of solver.xla from prior to 2000 sp3.
 
D

Dana DeLouis

[Solver.xla]!Excel4Functions!A31

I think this step is trying to add the Solver menu item to the Tools menu.
I'm not a CommandBars expert, so I would be curious if your Tools Menu item
is displayed. Since you wish to delete some commandbars, would this
workaround work? I don't know here, just guessing...

Have your Auto_Open() routine just call an On Timer macro for 1-2 seconds in
the future. This may allow Solver's Auto_Open to run its course and add the
Solver item to the menu. When your Auto routine kicks in later, perhaps you
can then delete the Tools item.

Your [Solver.xla]!Excel4Functions!A31 step is in Solver's Auto_Open where
this last step works on the menu. That is why you don't get this error
later. I would like to hear any feedback you can provide. Thanks. :>)

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


BU_Paul said:
The following error only happens when I start a new instance of excel
and will not re-occur if I close the workbook and re-open it.

When I open a particular application that has the solver reference
enabled, I get the following error:
Macro Error at Cell:
[Solver.xla]!Excel4Functions!A31

If I pick continue, there are no problems and all my macros work as
expected. If I pick step, I get Formula: =End.If() then I pick step
into and get Formula: =return(). I pick step into again and it
closes. And it still works fine after that.

This error occurs after the auto_open macro runs. Here is the macro:
Sub Auto_Open()
'
' Auto_Op Macro
' Macro recorded 3/23/2004
'

'
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
End With
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Worksheet Menu Bar").Controls(10).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(9).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(7).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(6).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(5).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(4).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(3).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(2).Delete
Application.CommandBars("Worksheet Menu Bar").Controls(1).Delete
ActiveSheet.Unprotect "prs"
Rows("28:42").Select
Selection.EntireRow.Hidden = False
Range("I24").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("I25:O42").Select
Selection.Sort Key1:=Range("O25"), Order1:=xlDescending,
Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Rows("28:42").Select
Selection.EntireRow.Hidden = True
Range("I7").Select
ActiveSheet.Protect "prs", DrawingObjects:=True, Contents:=True,
Scenarios:=True
End Sub

Any suggestions? Thanks.
Click to expand...
 
B

BU_Paul

Excellent. That is the problem. I was removing the tools menu from
the worksheet menu bar as part of the auto_open routine. When I added
a two second timer to the beginning of auto_open I didn't get the
error. Evidently this is enough time to allow solver's auto_open to
as you say "run its course". I experimented a little with the time,
setting the pausetime = 1 then 0.5 and finally 0.1. At such a quick
pause, I couldn't even tell there was a pause and it still worked
without the error. Thanks so much.

Dana DeLouis said:
[Solver.xla]!Excel4Functions!A31
Click to expand...

I think this step is trying to add the Solver menu item to the Tools menu.
I'm not a CommandBars expert, so I would be curious if your Tools Menu item
is displayed. Since you wish to delete some commandbars, would this
workaround work? I don't know here, just guessing...

Have your Auto_Open() routine just call an On Timer macro for 1-2 seconds in
the future. This may allow Solver's Auto_Open to run its course and add the
Solver item to the menu. When your Auto routine kicks in later, perhaps you
can then delete the Tools item.

Your [Solver.xla]!Excel4Functions!A31 step is in Solver's Auto_Open where
this last step works on the menu. That is why you don't get this error
later. I would like to hear any feedback you can provide. Thanks. :>)
Click to expand...
 
D

Dana DeLouis

Thank you for the feedback. Glad it works now. :>) Seems like this would
be an easy fix for Microsoft. (Don't know why a few lines of code still
have to be written in the old Excel4 way either).

It looks like you wish to remove all the menu items. If you get a chance,
could you test the following idea also? Not sure if this would work for
you.

Instead of deleting each Menu item, see if this works instead?? Set to
True when you finish.

.CommandBars("Worksheet Menu Bar").Enabled = False

On your QueryTable, see if there is an option to sort on your last column.
Having the Query do the sorting could save you from having to do it via a
macro.

I did a quick and dirty adjustment to the code if you find any ideas
helpful. Anyway, thanks for the feedback. Glad it works! :>)


Sub Auto_Open()
With Application
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False

.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False

' See if this works instead ??
.CommandBars("Worksheet Menu Bar").Enabled = False

ActiveSheet.Unprotect "prs"
Rows("28:42").Hidden = False
Range("I24").QueryTable.Refresh BackgroundQuery:=False

Range("I25:O42").Sort _
Key1:=Range("O25"), _
Order1:=xlDescending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Rows("28:42").Hidden = True
Range("I7").Select
ActiveSheet.Protect _
Password:="prs", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

End With 'Application

End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


BU_Paul said:
Excellent. That is the problem. I was removing the tools menu from
the worksheet menu bar as part of the auto_open routine. When I added
a two second timer to the beginning of auto_open I didn't get the
error. Evidently this is enough time to allow solver's auto_open to
as you say "run its course". I experimented a little with the time,
setting the pausetime = 1 then 0.5 and finally 0.1. At such a quick
pause, I couldn't even tell there was a pause and it still worked
without the error. Thanks so much.

[Solver.xla]!Excel4Functions!A31
Click to expand...

I think this step is trying to add the Solver menu item to the Tools menu.
I'm not a CommandBars expert, so I would be curious if your Tools Menu item
is displayed. Since you wish to delete some commandbars, would this
workaround work? I don't know here, just guessing...

Have your Auto_Open() routine just call an On Timer macro for 1-2 seconds in
the future. This may allow Solver's Auto_Open to run its course and add the
Solver item to the menu. When your Auto routine kicks in later, perhaps you
can then delete the Tools item.

Your [Solver.xla]!Excel4Functions!A31 step is in Solver's Auto_Open where
this last step works on the menu. That is why you don't get this error
later. I would like to hear any feedback you can provide. Thanks. :>)
Click to expand...
[/QUOTE]
 

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

Workbook_open not executing 1
MAJOR PROBLEM! --- Menu Bars don't unhide? 9
auto enable macros 5
Adding and Deleting custom commandbars 3
Stripping down excel 2
Shared Workbook 12
Menus in Excel 2007 20
Disable custom menu item based on visible cells 1

Top