UserForm - TextBox - Conditional Solver

[Polanskiman]

Hello everyone,

I have this userform with 2 option buttons and 3 texboxes (1,2 and 3). When the userform is validated by the user a macro is run. This macro contains a Solver among other lines of code.

When the userform is called, the value filled in by the user in textbox 2 (the actual name of the textbox is 'tbInputERatio' in code below) is used in the macro and solver mentioned above. This value basically needs to be comprised between 30% and 70% if not the solver will output erroneous results. The reason behind this is that the solver has specific constraints.

Here's what I would need:
1 - The userform to call a different macro (when validated) if the value in textbox 2 ('tbInputERatio') is comprised between 71% and 99% and call yet another macro if the value is equal to 100%. Perhaps it can all be kept within one macro but still I have no clue how to achieve that. If you see another alternative to my problem, please don't hesitate to share.

2 - The userform not to accept values below 30% in textbox 2 ('tbInputERatio2');

Is this possible? If so how?

Below is the VBA code of the userform as well as the code of the Macro:

USERFORM:

Option Explicit
Dim bCancel As Boolean

Property Get Cancel() As Boolean
    Cancel = bCancel
End Property

Private Sub obInputgrid_Click()
If obInputgrid.Value = True Then Worksheets("CAPITAL COST P1+P2").Range("E17").Value = 0
End Sub

Private Sub obInputturbine_Click()
If obInputturbine.Value = True Then Worksheets("CAPITAL COST P1+P2").Range("E17").Value = 1
End Sub

Property Get InputReturn() As Variant
    InputReturn = tbInputLPGPrice.Value
  End Property

Property Get InputReturn2() As Variant
    InputReturn2 = tbInputERatio.Value
  End Property
  
Property Get InputReturn3() As Variant
    InputReturn3 = tbInputTax.Value
End Property

Private Sub cbCancelERatio_Click()
    bCancel = True
    Me.Hide
End Sub

Private Sub cbProceedERatio_Click()
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
        Cancel = True
        MsgBox "Action not allowed." & vbCr & "" & vbCr & "Press the 'Cancel' button if you wish to terminate the process."
    End If
End Sub

Private Sub UserForm_Initialize()
    cbProceedERatio.Enabled = False
End Sub

Private Sub tbInputLPGPrice_Change()
    cbProceedERatio.Enabled = CBool(Len(tbInputLPGPrice.Text) * Len(tbInputERatio.Text) * Len(tbInputTax.Text))
End Sub

Private Sub tbInputERatio_Change()
    cbProceedERatio.Enabled = CBool(Len(tbInputLPGPrice.Text) * Len(tbInputERatio.Text) * Len(tbInputTax.Text))
End Sub

Private Sub tbInputTax_Change()
    cbProceedERatio.Enabled = CBool(Len(tbInputLPGPrice.Text) * Len(tbInputERatio.Text) * Len(tbInputTax.Text))
End Sub

MACRO:

   Sub ShowFormDebtEquitySensitivity()
        'http://peltiertech.com/Excel/SolverVBA.html
        
    Application.ScreenUpdating = False
        Dim fmForm As FrmDERatio 'Object variable for form
        Set fmForm = New FrmDERatio 'create new instance of the form
    With fmForm
          .Show
            If Not .Cancel Then
    Application.ScreenUpdating = False
            Sheets("DATA").Range("F24").Value = val(.InputReturn)
            Call A1_Reset_Prodction_Cost_Line24
            Call B1_Prod_Cost_Goal_Seek_Phase1_Copy_Paste_Value
            Call C1_Prod_Cost_Goal_Seek_Phase2_Copy_Paste_Value
            Call D1_Prod_Cost_Goal_Seek_310Days_Copy_Paste_Value
            Call E1_Delete_Marginal_Prod_Cost_Cell_P24
        Sheets("DATA").Range("F46").Value = val(.InputReturn3) & "% "
        Sheets("CAPITAL COST SUM").Range("AZ100").Value = val(.InputReturn2) / 100
        Sheets("CAPITAL COST SUM").Range("C31:C32,E37").ClearContents
        Sheets("CAPITAL COST SUM").Select
    Application.Run "SolverReset"
    Application.Run "SolverOk", "$C$42", 3, val(.InputReturn2) / 100, "$C$31,$C$32,$E$37"
    Application.Run "SolverAdd", "$C$31", 3, "0"
    Application.Run "SolverAdd", "$C$32", 3, "0"
    Application.Run "SolverAdd", "$D$31", 3, "1000000"
    Application.Run "SolverAdd", "$D$32", 3, "1000000"
    Application.Run "SolverAdd", "$G$37", 2, "0"
    Application.Run "SolverOptions", 100, 1000, 1e-15, False, False, 1, 1, 1, 5, False, 1e-05, False
    Application.Run "SolverSolve", True
    Application.Run "SolverFinish", 1
    Application.ScreenUpdating = True
    Sheets("PRICES").Select
    MsgBox "- The Marginal Production Costs have been calculated." & vbCr & "" & vbCr & "- The LPG Cost has been set to: " & .InputReturn & " USD/Ton " & vbCr & "" & vbCr & "- The Debt / Equity Ratio has been set to:  " & 100 - .InputReturn2 & "% " & "/ " & .InputReturn2 & "% " & vbCr & "" & vbCr & "- The Corporate Tax Rate has been set to: " & .InputReturn3 & "% " & vbCr & "" & vbCr & "Click OK to continue with the Simulation."
    End If
End With
End Sub

Thank you very much for the help provided.

 

[Polanskiman]

Just wanted to clarify my first point.

The idea is that depending the value input in textbox 2 ('tbInputERatio') a solver with different constraints needs to be called (the rest of the macro would remains basically the same), reason why I though calling a different macro would be the solution, but perhaps it's possible to keep all this within one macro.

Thanks for the help.

 

[Polanskiman]

Anyone?

 

[Polanskiman]

Re-Anyone?