Posts by armyguy297

    Re: Help Fixing VBA Code


    I had actually tried declaring variables yesterday. I will try working with the help you have provided and maybe I can figure it out. I made a lot of progress yesterday on my project. No need to act high and mighty... I appreciate your help and patience as you share your wealth of information with someone who desires to learn as well. No offense was intended yesterday. Comments such as "Haven't you already asked that question?" Is a childish move in my opinion. Here I am in Afghanistan frustrated with work and my inability to write codes efficiently, and then I get talked to like this is something all people are supposed to know. To me it is Greek, but I am determined to continue playing with code, reading, and requesting the knowledge of others like yourself to help me out along the way. If you don't understand one teacher why not seek out another. I did it all through college. If no one is answering I will continue to seek help. Or I could just pay for someone to just do it for me... That always works, but I lose the value of obtaining knowledge. Again thanks for your help.... It is greatly appreciated.


    *EDIT* Created a new profile and posted profanities.

    So after a long day of trying to get help and get my codes fixed... I think I have come close... Could someone help me to fix the funtion and get it to call from the click event of Calculate_Mortgage_1 button in userform1? The function is at bottom of codes and I tried to call it but keep getting error messages. Thanks!


    Here is the excel document -----> forum.ozgrid.com/index.php?attachment/46267/

    Re: Syntax for converting textbox value to number for performing calculations on it.


    Thanks AAE....


    Can someone explain why I get a type mismatch for the following code? I am trying to build a userform mortgage comparison calculator.


    Code
    Private Sub Calculate_Mortgage_1_Click()
    Dim i As Integer
    Dim n As Integer
    i = tbInterestRate1 / 12
    n = cbTerm1 * 12
    Me.tbPurchasePrice1.Value = Format$(Val(Sheets("Dashboard").Range("b26").Value), "$#,#.00")
    tbAmountFinanced1 = tbPurchasePrice1 - (tbPurchasePrice1 * cbDownPayment1)
    tbMortgagePayment1 = tbAmountFinanced1 * [i(1+i)^n] / [(1+i)^n - 1]
    End Sub


    Formula for Mortgage Payment:


    M = P [ i(1 + i)n ] / [ (1 +i)n - 1]


    The error happens in last line... am I on the right track to this? Is it because the textboxes are strings and the formula Dim statements are integers?

    Re: Syntax for converting textbox value to number for performing calculations on it.


    Thanks AAE....


    Can someone explain why I get a type mismatch for the following code? I am trying to build a userform mortgage comparison calculator.


    Code
    Private Sub Calculate_Mortgage_1_Click()
    Dim i As Integer
    Dim n As Integer
    i = tbInterestRate1 / 12
    n = cbTerm1 * 12
    Me.tbPurchasePrice1.Value = Format$(Val(Sheets("Dashboard").Range("b26").Value), "$#,#.00")
    tbAmountFinanced1 = tbPurchasePrice1 - (tbPurchasePrice1 * cbDownPayment1)
    tbMortgagePayment1 = tbAmountFinanced1 * [i(1+i)^n] / [(1+i)^n - 1]
    End Sub


    Formula for Mortgage Payment:


    M = P [ i(1 + i)n ] / [ (1 +i)n - 1]


    The error happens in last line... am I on the right track to this?

    Re: Syntax for converting textbox value to number for performing calculations on it.


    Here is my worksheet... I want to make things show as percents or whole dollar amounts with commas to represent thousands. I can be given the code and still need to be told where to insert it bc I do not want to mess other codes that seem to be working. When the formulas calculate ( I have several calculate buttons) the ones that are coded seem to be working. but with no format. I do not know format properties.


    Here is what stumps be as you can see this is where my userform 1 stops calculating (after filling in yellow boxes and clicking calculate buttons....


    I know Excel worksheet functions in and out... say PMT (Rate, Nper, PV, FV, Type) This is one I need for my calculator. But I do not know how to combine this standard function with code to have VBA retrieve these values for me.

    Re: Syntax for converting textbox value to number for performing calculations on it.


    Oh working codes as in ones that are already written... I agree except I do not think this means anything to a beginner.


    "Val(Replace(txtbox.Value,"%","")) / 100 * Val(txtbox2.Value)" or


    ".TextBox3.Value = Val(Replace(Replace(.TextBox1.Value, ",", ""), "$", "")) _"


    It is like me telling you "Salam alakem, schlonek, Zien hamdala inta schlonek?" or maybe rafriedic, nasilidic or any other arabic phrases.


    This is what it looks like to me...


    I see value knowing it means the value of a certain cell or textbox (by name) and then ":"::>.;;''/{{}}{?>>">"?}


    Here is what I have written so far:


    Re: How to Use a Function inside of VBA UserForm


    Okay it lost the dollar sign in answer, but I can live with it...


    So the code you sent for referencing a cell in Sheet "Pro Forma" will work if I use a worksheet function on sheet and then use the code to place the value in it for me correct?

    Re: How to Use a Function inside of VBA UserForm


    Yes... lol damn and that actually makes sense... let me give it a shot and I'll get back to you on it... Thanks... Now how do you know what to replace or where to put it. I went to textbox 1 rightclicked and went to view code. No clue after that. Does it replace everything for text box 1 or do you simply insert it somewhere so that it can be deleted and manually entered?

    Re: How to Use a Function inside of VBA UserForm


    Looks great! One last thing, say you want the RATE function to operate similat to above, but instead of typing you want code textbox 1's value to be equal to the value of say sheet "Pro Forma" range B27. When you type a function such as RATE (Range ("Pro Forma"B27.Value), Range("Pro Forma"B32.Value * 12), etc. I get tons of errors. This is just an example, but if you would prefer I will create an exact scenario and attach it. It has been giving me a headache for three days now lol. Thanks for the above code it works beautifully.

    Re: How to Use a Function inside of VBA UserForm


    Code
    [COLOR=#0000ff]Private[/COLOR] [COLOR=#0000ff]Sub[/COLOR] TextBox2_Exit([COLOR=#0000ff]ByVal[/COLOR] Cancel [COLOR=#0000ff]As[/COLOR] MSForms.ReturnBoolean) 
        [COLOR=#0000ff]With[/COLOR] Me 
            [COLOR=#0000ff]With[/COLOR] .TextBox2 
                .Value = Val(Replace(.Value, "%", "")) & "%" 
            [COLOR=#0000ff]End With[/COLOR] 
            .TextBox3.Value = Val(Replace(Replace(.TextBox1.Value, ",", ""), "$", "")) _ 
            * Val(Replace(.TextBox2.Value, "%", "")) / 100 
        [COLOR=#0000ff]End With[/COLOR] 
    [COLOR=#0000ff]End Sub[/COLOR]




    On the third from bottom line just above End With There is a multiplication symbol. I changed it to show division. I need textbox1 (example: 5,678) to be divided by textbox2 (8% or 0.08) which comes out to $70,975 but it reads 7.0975.
    For some reason the decimal is way off.


    I believe that I understand your remarks regarding format. I do not know squat about code... but the way I understand the formatting of textbox3 is that because it is being used as calculation it cannot show formatting since it needs to show the original value... If there were to be a small calculate button then the textbox three can be formatted? If so that would be great! Thanks

    Re: How to Use a Function inside of VBA UserForm


    WOW that is some tricky looking code. I changed the operator in private sub textbox2 exit to show division instead of multiplication. It reads 7.0975 instead of $70,975. Any idea why? The answer is correct except that the decimal is 4 places to the left. Thanks for your help.

    Re: How to Use a Function inside of VBA UserForm


    I attached it... UserForm2... It would be helpful if textbox 1 displays with dollar sign... textbox two displays the number I enter as a percentage and it will calculate as a percent as well.The needed formula is for TextBox1 / TextBox2 with the answer populating to text box3. For Example... $34,000/8%=in text box 3. Text three should also display answer with dollar sign.
    The sheet is below.


    ---------> forum.ozgrid.com/index.php?attachment/46242/

    Re: How to Use a Function inside of VBA UserForm


    Okay that makes sense to me and it allowed me to enter the numbers...


    1) Can I make textbox display 8% when I type "8" and will it calculate as "0.08"
    2) How do I make Textbox 3 Display the answer? How do I code(Say: Command button1 ) to perform calculation and enter the answer into textbox 3
    3) Is it possible to make a value from Sheet 1 populate to the textboxes respective positions on userform when userform opens?


    Thanks for the help!