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.

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)ⁿ ] / [ (1 +i)ⁿ - 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.

Oh is it because the values in textboxes are strings and my formula numbers 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.

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)ⁿ ] / [ (1 +i)ⁿ - 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:

Private Sub Calculate_Mortgage_1_Click()
Me.tbPurchasePrice1.Value = Format$(Val(Sheets("Dashboard").Range("b26").Value), "$#,#.00")
tbAmountFinanced1 = tbPurchasePrice1 - (tbPurchasePrice1 * cbDownPayment1)
tbMortgagePayment1 =
End Sub
Private Sub Calculate_Mortgage_2_Click()
Me.tbPurchasePrice2.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
tbAmountFinanced2 = tbPurchasePrice2 - (tbPurchasePrice2 * cbDownPayment2)
End Sub
Private Sub Calculate_Mortgage_3_Click()
Me.tbPurchasePrice3.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
tbAmountFinanced3 = tbPurchasePrice3 - (tbPurchasePrice3 * cbDownPayment3)
End Sub
Private Sub Calculate_Mortgage_4_Click()
Me.tbPurchasePrice4.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
tbAmountFinanced4 = tbPurchasePrice4 - (tbPurchasePrice4 * cbDownPayment4)
End Sub
Private Sub Calculate_All_Mortgages_Click()
Me.tbPurchasePrice1.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
Me.tbPurchasePrice2.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
Me.tbPurchasePrice3.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
Me.tbPurchasePrice4.Value = Format$(Val(Sheets("Dashboard").Range("B26").Value), "$#,#.00")
tbAmountFinanced1 = tbPurchasePrice1 - (tbPurchasePrice1 * cbDownPayment1)
tbAmountFinanced2 = tbPurchasePrice2 - (tbPurchasePrice2 * cbDownPayment2)
tbAmountFinanced3 = tbPurchasePrice3 - (tbPurchasePrice3 * cbDownPayment3)
tbAmountFinanced4 = tbPurchasePrice4 - (tbPurchasePrice4 * cbDownPayment4)
End Sub
Private Sub Clear_All_Click()
' Clears Purchase Price Text Boxes
tbPurchasePrice1.Text = ""
tbPurchasePrice2.Text = ""
tbPurchasePrice3.Text = ""
tbPurchasePrice4.Text = ""
' Clears Down Payment Combo Boxes
cbDownPayment1.Text = ""
cbDownPayment2.Text = ""
cbDownPayment3.Text = ""
cbDownPayment4.Text = ""
' Clears Amount Financed Text Boxes
tbAmountFinanced1.Text = ""
tbAmountFinanced2.Text = ""
tbAmountFinanced3.Text = ""
tbAmountFinanced4.Text = ""
' Clears Interest Rate Text Boxes
tbInterestRate1.Text = ""
tbInterestRate2.Text = ""
tbInterestRate3.Text = ""
tbInterestRate4.Text = ""
' Clears Term Combo Boxes
cbTerm1.Text = ""
cbTerm2.Text = ""
cbTerm3.Text = ""
cbTerm4.Text = ""
' Clears Mortgage Payment Text Boxes
tbMortgagePayment1.Text = ""
tbMortgagePayment2.Text = ""
tbMortgagePayment3.Text = ""
tbMortgagePayment4.Text = ""
' Clears Net Operating Income Text Boxes
tbNOI1.Text = ""
tbNOI2.Text = ""
tbNOI3.Text = ""
tbNOI4.Text = ""
' Clears Annual Debt Service Text Boxes
tbDebtService1.Text = ""
tbDebtService2.Text = ""
tbDebtService3.Text = ""
tbDebtService4.Text = ""
' Clears Pre Tax Cash Flow Text Boxes
tbPreTaxCashFlow1.Text = ""
tbPreTaxCashFlow2.Text = ""
tbPreTaxCashFlow3.Text = ""
tbPreTaxCashFlow4.Text = ""
End Sub


Private Sub tbAmountFinanced1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    tbAmountFinanced1 = tbPurchasePrice1 - (tbPurchasePrice1 * cbDownPayment1)
End Sub

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

what do you mean by working codes? Writing them? I am in a personal project. But without references I cannot write complete syntaxes. I have already bought like 8 excel VBA books but most of them teach in a way of if you want this do this... it does not elaborate on how to customize the codes.

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

I am looking for a reference that can act as instructions for writing my own custom code. Forget it. Just seeing the code does not help me understand what the code means. I am just tryin to learn.

Can someone write a generic code with comments that will explain how to convert entries by user into a textbox can be converted to a number for performing functions?

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

Okay so then I lose the calculation... see userform 2 ----------> forum.ozgrid.com/index.php?attachment/46254/

Re: How to Use a Function inside of VBA UserForm

Got it. Thanks a lot...

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

[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

It did not populate textbox 3 when I entered values for the other two textboxes.
How do I code the "Calculate" button to perform the calculation and send answer to textbox 3?

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!