UserForm - TextBox - Conditional Solver

Polanskiman

New Member
Joined
Nov 29, 2011
Messages
47
Office Version
365, 2016
Platform
Windows, MacOS
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: :confused:
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:
Code:
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:
Code:
   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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Polanskiman

New Member
Joined
Nov 29, 2011
Messages
47
Office Version
365, 2016
Platform
Windows, MacOS
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,113
Messages
5,466,746
Members
406,495
Latest member
Arlind Elezi

This Week's Hot Topics

Top