Userform refresh after change source cell

[Actuary89]

Hi all,
I'm new in the community and I wanted to share a problem I'm experiencing with a file: I've a userform update issue and I'm struggling to find an easy solution (even if I'm pretty sure it's there)

My file is composed by 2 spreadsheet: 28113
- "Pricing Summary - GL" where the user can see and modify data -> the aim of this sheet is to compare the value inserted by the user (AP) with a value calculated by the file (TP); this will produce a ratio named APTP
- "Calculation" where the user can't see nor modify data (those data are used to Initialize the Userform) -> the aim of this sheet is to compare the ratio calculated in the other sheet (APTP) with the same ratio that was calculated the previous year (last year APTP); this will produce another ratio named ERC

WHAT I'VE DONE:
a. create a userform that shows 3 KPIs
b. 2 KPIs (last year APTP and ERC) will be not editable and are linked to 2 specific cells in "Calculation" (done)
c. 2 KPIs (ERC_Adj and Rationale) are blank and editable by the user (done), when the user clicks OK the Userform will copy and store that KPIs as well

OBJECTIVE:
d. when the user will modify the AP, a new APTP is calculated; as a result a new ERC is calculated as well. When I open the userform I'd like to see the updated ERC but, for some reason, the userform keeps on showing the initial ERC

Here's the code within the Userform:


Private Sub UserForm_Initialize()
tb_APTP.value = Sheets("Calculation").range("fld_APTP_lastyear").value * 100
tb_ERC.value = Sheets("Calculation").range("fld_ERC").value
tb_ERC_Adj.value = Sheets("Calculation").range("fld_ERC_Adjusted").value * 100
tb_Rationale.value = Sheets("Calculation").range("fld_ERC_Rationale").value
End Sub


Private Sub Cmd_Ok_Click()Dim StrAnswer As String
If Me.tb_ERC_Adj <> "" And Me.tb_ERC_Adj <> Me.tb_ERC And Me.tb_Rationale = "" Then
StrAnswer = MsgBox("Provide a rationale to explain ERC difference", vbExclamation + vbOKOnly, "Warning - Mandatory Rationale")
Else
If Me.tb_ERC_Adj.value = "" And Me.tb_ERC = 0 Then
Sheets("Calculation").range("fld_ERC_Adjusted") = 0
ElseIf Me.tb_ERC_Adj.value = "" Or Me.tb_ERC_Adj.value = 0 And Me.tb_ERC <> 0 And Me.tb_Rationale = "" Then
Sheets("Calculation").range("fld_ERC_Adjusted") = Sheets("Calculation").range("fld_ERC") / 100
ElseIf Me.tb_ERC_Adj.value = Me.tb_ERC Then
Sheets("Calculation").range("fld_ERC_Adjusted") = Sheets("Calculation").range("fld_ERC") / 100
Else
Sheets("Calculation").range("fld_ERC_Adjusted") = Me.tb_ERC_Adj.value / 100
End If
Sheets("Calculation").range("fld_ERC_Rationale") = Me.tb_Rationale.value
Sheets("Calculation").range("fld_ERC_Timestamp") = Now
Me.Hide
End If
End Sub

And here's the code within the Sheet for the Worksheet_Change, where P29 is the cell that the user can modify:

Private Sub Worksheet_Change(ByVal Target As range)
Select Case Target.address
Case "$P$29"
PriceDate_L1 (range("M7").value)
End Select
End Sub


Sub PriceDate_L1(selected As String)
If Sheets("Pricing Summary - GL").range("P24").value = "" Then
Select Case selected
Case "No Errors Detected"
Sheets("Pricing Summary - GL").range("P24").value = Now
Call LoadForm_frmERC
Case Else
'do nothing
End Select
Else
Call LoadForm_frmERC
End If
End Sub

I hope I was clear enough but please, in case I missed any piece, just let me know (I should have been able to attach the file example for a better comprehension).
Thanks!

[Kenneth Hobs]

Textbox values are strings. If you use those values but they are numbers, use VAL(TextBox1)+Val(TextBox2) for example.

Check that automatic calculation is set of force calculation in the macro.

[SamT]

UserForm_Initialize() is only run the first time the Form is shown

Other than that, I am not going thru 750+ Worksheet Names to find an error in your code. The two don't even belong together.

Not to mention that you have Merged Cells on the Worksheet, which VBA really hates working with.

I do like the method you used to create all the Names. They make good sense.
IMO, everything visible or accessable from the Sheet View is the sole domain of the User and Sheet Developer; Only what is visible from the VBA view is the domain of the VBA Coder.

As an example of No Sheet Names in Code:
In VBA edit Sheet9's Code Name to "Calculation"
In Calculation's Code page add
Public Property Get ERC() As String 'Google "Property Get Let Set"
If Not IsError(Range("B3")) then
ERC = CStr(Range("B3").Value) 'CStr 'cuz TextBox Values are Strings
Else
ERC = "Oopsie!" 'Stupid Error Message
End If
End Property

In The Form's Initialization Sub, edit
tb_ERC.Value = Sheets("Calculation").Range("fld_ERC").Value
To
tb_ERC.Value = Calculation.ERC

[SamT]

One Kludge to your current code might be:
In Worksheet_Change sub

frmERC.Reset_ERC NewERCValue
In the UserForm

Public Function Reset_ERC(NewValue)
tb_ERC.Value = NewValue
End Function