Pulling through figures from excel, on a UserForm, in realtime

[cmerrick]

Background
I have a user form that is acting as an over lay to fairlycomplex calculator that I already have built in Excel.
The UserForm is tomake it easier for the user to input info into the calculator so all I’m reallydoing is sending info back and forth between the UserForm, excel is doing allof the calculations.
I know that this might be the ‘long way around’ but I don’t reallyfeel like re creating the entire calculator in VBA.

Ok so to my question –how can I get a percentage value to update in real time on the user form when theform itself is not doing to calculation.

My example –

The two private subs below collection two value (PropertyPrice and Loan Amount) and send these value to excel cells (D5 and D6respectivley)

[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub ValuationTB_AfterUpdate()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'format ValuationTB[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    ValuationTB.Value= Format(ValuationTB.Value, "£#,##0.00")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'send data to Form Validation D5[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Sheets("FormValidation").Range("D5") = ValuationTB.Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub LoanAmountTB_AfterUpdate()[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'format LoanAmountTB[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    LoanAmountTB.Value= Format(LoanAmountTB.Value, "£#,##0.00")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'send data to Form Validation D6[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Sheets("FormValidation").Range("D6") = LoanAmountTB.Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

The below code is part of the Userform_Initialize (which Ithink is the wrong place for it) but it basically populates the LTV (loan toValue) Text Box (LTVTB) with the calculation - ValuationTB / LoanAmountTB = LTVTB as a Percentage

So if - ValuationTB = £250,000 and LoanAmountTB = £150,000the LTVTB should be 60%

[FONT=Calibri][SIZE=3][COLOR=#000000]'populate LTVTB with % from Form Validation D9[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'format LTVTB as %[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With LTVTB[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    .Value =Sheets("Form Validation").Range("D9")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    .Value =Format(Me.LTVTB.Value, "0%")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

I believe I’m going wrong because the above code for the LTVTBis running on Initialize when it needs to be running after the values of ValuationTBand LoanAmountTB are completed.

As I said, I don’t really want to programme the user form withcalculations I just want it to pull the info through from Excel (which isalready doing them) in real time.

Can someone help me out with this one?

 

[dmt32]

Hi,
one approach maybe would be to place your event codes in a common code which will update your % textbox


Untested but try following

Private Sub ValuationTB_AfterUpdate()
    LTVCalc
End Sub


Private Sub LoanAmountTB_AfterUpdate()
    LTVCalc
End Sub


Private Sub LTVCalc()
 Dim wsFormValidation As Worksheet
 
 Set wsFormValidation = Worksheets("Form Validation")
 
  With Me.ValuationTB
    .Value = Format(.Value, "£#,##0.00")
    wsFormValidation.Range("D5") = .Value
  End With
  
  With Me.LoanAmountTB
    .Value = Format(.Value, "£#,##0.00")
    wsFormValidation.Range("D6") = .Value
  End With
  
    LTVTB.Value = wsFormValidation.Range("D9").Text
End Sub

Place all codes in your UserForms Code page

Note: I have assumed that the range D9 is formatted as % in which case, using the Range.Text property should return what you see in the cell to your textbox

Dave