Problem with BeforeUpdate and initialize

[robfo0]

Hi everyone, yet another userform question.

I have a label in my form which is updated when a value is changed in a certain textbox. To do this, i use the textbox_BeforeUpdate (doing this calls a function to calculate a payment). But I'm having a problem. When I initialize the textbox in Userform_Initialize, it tries to do the BeforeUpdate process also. this is a problem because not all the variables have been defined in the initialization.

My question is: Is there a way to disable things such as BeforeUpdate when executing Userform_Initialize so that I can properly get all my variables defined?

Thanks

[dk]

Hi,

What's your code? I tried to duplicate what you said but the Before_Update event didn't fire when I placed some code to change the textbox in the form's initialise event.

Dan.

[robfo0]

Here is the whole code Im using. I'm trying to do the opposite of you, i DONT want the beforeupdate (actually the Change event) to execute until after the initialize userform is DONE. Anyway, here it is :

Private Sub UserForm_Initialize()

BORR_NAME = "BORR"
COBORR_NAME = "COBORR"
CONTRACTOR = "CONTRACTOR"
TYPE_IMPROV = "WINDOWS"
DOWN_PMT = 0
FIRST_TERM = 120

If FIRST_TERM.Value > 61 Then
FIRST_RECFEE = "$30.00"
Else
FIRST_RECFEE = "$20.00"
End If

FIRST_RATE.Value = Format(Worksheets("Sheet1").Range("RATE"), "0.00%")
AMT_FINAN = Format(Worksheets("Sheet1").Range("TESTVALUE").Value + FIRST_RECFEE, "$#,##0.00")
FIRST_DAYSDEF = InputBox("days def")


Call CALC_PAYMENT

End Sub


Private Sub FIRST_DAYSDEF_Change()
If FIRST_DAYSDEF = "" Then
FIRST_DAYSDEF = 0
Else
End If
Call CALC_PAYMENT
End Sub


Private Sub FIRST_RECFEE_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
FIRST_RECFEE = Format(FIRST_RECFEE, "$#,##0.00")
End Sub

Private Sub FIRST_RECFEE_Change()
If FIRST_RECFEE = "" Then
FIRST_RECFEE = 0
Else
Call CALC_AMT_FINAN
End If
Call CALC_PAYMENT
End Sub

Function CALC_PAYMENT()
If FIRST_DAYSDEF = 0 Then
FIRST_NEW_PMT = Format(Pmt(Worksheets("Sheet1").Range("RATE") / 12, FIRST_TERM, -AMT_FINAN), "$#,##0.00")
Else
FIRST_NEW_PMT = Format(AMT_FINAN * ((Pmt(Worksheets("Sheet1").Range("RATE") / 12, FIRST_TERM, -100) / 100) + (Worksheets("Sheet1").Range("RATE") / 365) * (FIRST_DAYSDEF / FIRST_TERM)), "$#,##0.00")
End If
End Function

Function CALC_AMT_FINAN()
AMT_FINAN = Format(Worksheets("Sheet1").Range("TESTVALUE") + FIRST_RECFEE, "$#,##0.00")
End Function

So whats happening is CALC_PAYMENT is getting called before FIRST_DAYSDEF is defined and its causing problems Help! thanks!

[dk]

Hi,

I'm still not entirely sure if I understand the problem. There are problems in that you're changing the format of the textbox to "$#,##0.00" (effectively making the value of FIRST_RECFEE a string, not number) and then trying to perform a numerical calculation on it:-

AMT_FINAN = Format(Worksheets("Sheet1").Range("TESTVALUE") + FIRST_RECFEE, "$#,##0.00")

This is giving you a type mismatch error yes?

You could replace it with this line which converts FIRST_RECFEE to a numerical value.

AMT_FINAN = Format(Worksheets("Sheet1").Range("TESTVALUE") + Replace(FIRST_RECFEE, "$", "") * 1, "$#,##0.00")

I've got the format of your userform on my machine now so if this isn't the problem please post back and I'll have another look.

Regards,
Dan

[dk]

Also, you're going to get problems doing this:-

FIRST_RECFEE = "$30.00"

FIRST_RECFEE value is now a string and you'll have to convert it before you can carry out any calculations using it. I understand that you want the textboxes to be formatted but you'll have to convert the values before you can do anything with them.

Regards,
Dan

[robfo0]

Dan,

Thanks for the response. Actually, you answered one of my previous posts. I thought formatting the formula simply changed the look, i didnt know it changed it to a string. That answers a lot of questions.

But I still have this problem. Whats happening is that im trying to initialize all my values in the Initialize Userform. In addition, i have calculations that execute on change and also beforeupdate so the form is always correct when the user changes values. The problem im running into is that when i initialize a variable, for example;

If i define FIRST_RECFEE first, it puts in the value, but then doing this causes the Change event to trigger (which contains a reference to the function CALC_PAYMENT, which contains the variable FIRST_DAYSDEF). But the variable FIRST_DAYSDEF has nothing in it because it hasnt been defined yet because it was interrupted by the Change event. So, my first idea was to prevent the Change or Beforeupdate Events from triggering while initializing so that this problem doesnt occur.

Get what I'm saying?

If you had another idea, im totally open to suggestions

Thanks again