Problem with BeforeUpdate and initialize

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
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 :)
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top