Code not working

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
Okay, that's not entire true. This code works when I step through it, but not when I run it with F5... is it moving too fast?

Code:
If CheckSolver Then
    a = MsgBox("Solver installed! Do you wish to utilize advanced functionality?" & Chr(10) & "(Choosing Yes will load solver, No will load goalseek)", vbYesNo, "Choose your Optimization Method")

        If a = 6 Then Sheets("Lookup Values & Tables").Range("SolSeek") = "Solver"

End If

the 2nd if statement is the one that's not working for me. It will change based on the msgbox input when i step through it, but not when I run it. Come to think of it, it might not always work when i step through it either... I'll have to check.

Do I need to put a break/pause in there somehow?
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
okay, I have changed the code, but I still have the issue that it only works when I step through it.

Here's everything:

Code:
Function CheckSolver() As Boolean
Application.ScreenUpdating = True
Dim bSolverInstalled As Boolean
Dim a As Integer
           
           
            Sheets("Assumptions and Inputs").Select
            Range("D12").Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="Goalseek"
                End With

If gbDebug Then Debug.Print Now, "CheckSolver "
    CheckSolver = True
    
On Error Resume Next
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
    Err.Clear

If bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = False
    bSolverInstalled = Application.AddIns("Solver Add-In").Installed
End If

If Not bSolverInstalled Then
    Application.AddIns("Solver Add-In").Installed = True
    bSolverInstalled = Application.AddIns("Solver Add-in").Installed
End If

If Not bSolverInstalled Then
    MsgBox "Solver Not Found, Enhanced Features Disabled!", vbCritical
    CheckSolver = False
End If

If CheckSolver Then
MsgBox "Solver Installed!  Additional Optimization Inputs Available!", vbExclamation, "Solver Found!"
             Sheets("Assumptions and Inputs").Select
            Range("D12").Select
                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Solver, Goalseek"
                End With
End If


On Error GoTo 0

End Function
 
Upvote 0
Could you explain what the code is meant to do? In plain english & preferably simple steps. By the way, I can't immediately see any reason why the assignment of the message box return to a shoul fail.
 
Upvote 0
Essentially, I'm running the function posted a few posts ago when my workbook opens, to check to see if the user has Solver installed. If the user does, then it sets a data validation drop down in cell D12 of the "Assumptions and Inputs" sheet to include both Goalseek and Solver. If the user does not have Solver installed, I only want to list Goalseek in this cell.

In other words: If the user has solver, let them pick between solver and goalseek on Assumptions and Inputs Cell D12. If they don't have solver, force that cell to be "goalseek"

The above code works perfectly when stepping through it, but does not work when I simply run the code (or when I open the workbook). Every line appears to be executing correctly in that case, I even get the "Solver Installed! Additional Optimization Inputs Available!" Message to pop up, however, the D12 Cell's Data validation does not include solver in the list.
 
Upvote 0
Essentially, I'm running the function posted a few posts ago when my workbook opens, to check to see if the user has Solver installed. If the user does, then it sets a data validation drop down in cell D12 of the "Assumptions and Inputs" sheet to include both Goalseek and Solver. If the user does not have Solver installed, I only want to list Goalseek in this cell.

In other words: If the user has solver, let them pick between solver and goalseek on Assumptions and Inputs Cell D12. If they don't have solver, force that cell to be "goalseek"

The above code works perfectly when stepping through it, but does not work when I simply run the code (or when I open the workbook). Every line appears to be executing correctly in that case, I even get the "Solver Installed! Additional Optimization Inputs Available!" Message to pop up, however, the D12 Cell's Data validation does not include solver in the list.
 
Upvote 0
How are you calling it? ie from what sub in your workbook? I am slightly surprised that it isn't actually in a sub itself rather than a function...
 
Upvote 0
it probably should be a sub. I'm calling it from the workbook open event.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top