MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Solver add-in menu item disappears

  Asked By: Charlie    Date: Feb 21    Category: MS Office    Views: 2370

My code adds lots of extra menu items to the Excel menu bar. When the
workbook loads/exits, it resets the normal Excel menu bar.

Sub Reset_CommandBars()
' In case user has reset menus already
On Error Resume Next
With Application.CommandBars("CustomMenu")
.Visible = False
End With
With Application.CommandBars("Worksheet Menu Bar")
End With
On Error GoTo 0
End Sub

Thing is, I lose my Solver add-in the whole time. My way to get it back
is as follows:
Sub Restore_Solver_Menu_Item()
' Unload the solver add-in, and install it again to
' have it show under the Tools menu again.
AddIns("Solver Add-in").Installed = False
AddIns("Solver Add-in").Installed = True
End Sub

But what I really want to do is the following:
1) Check whether the solver add-in is activated or not.
2) If already activated, is the option in the Tools menu?
3) If not in the menu, add it.

Any ideas, anyone?



1 Answer Found

Answer #1    Answered By: Tiaan Thompson     Answered On: Feb 21

The following might help.

Sub blah()
'Check whether add-in  installed, if not install  it.
If Not AddIns("Solver Add-in").Installed Then AddIns("Solver
Add-in").Installed = True

'check if menu  item is present, if not add  one.
If Not SolverControlPresent Then
Set MenuItem = CommandBars("Worksheet Menu
With MenuItem
.Caption = "Solver (mine)"
.OnAction = "'C:\Program Files\Microsoft
End With
End If
End Sub

You'll need the SolverControlPresent function to go with that:

Function SolverControlPresent()
SolverControlPresent = False
For Each ctl In CommandBars("Worksheet Menu
If ctl.Caption = "Sol&ver..." Then SolverControlPresent = True
End Function

Be aware that I've named my added menu item  "Solver (mine)" to
distinguish it from the existing one I have. Also, the OnAction line
may be a different path on your machine.

To delete any menu items  use the likes of:
CommandBars("Worksheet Menu Bar").Controls("&Tools").Controls("Solver
in the immediate pane.

There is a way to place the menu item in the right place within the
dropdown menu but I'm out of time  at the moment.

Didn't find what you were looking for? Find more on Solver add-in menu item disappears Or get search suggestion and latest updates.