Problem with BeforeUpdate and initialize
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Problem with BeforeUpdate and initialize

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com