-
Feb 25th, 2012, 08:43 PM
#1
Thread Starter
New Member
[RESOLVED] Excel 2007 IF statement Issue
Hi, this is my first post. I am new to visual basic. I built a small applet to handle transactions (Debits and Credits on excel) the credit function allows the user to input the credit amount and then the code updates all relevant cells on the spreadsheet and works out a closing balance.
What I would like to do for the credit function is
1. have a check that works out whether the credit amount will take the user into a negative figure.
2. if yes then display a message box warning the user and asking them if they want to continue.
3. if yes then update the spreadsheet fields.
4. if no then end.
I tried doing an if statement but it will only run whatever if i put first i.e. "if vbno then end" is the first statement it will only run this regardless of what button is pressed.
The code i have used is:
Private Sub CommandButton3_Click()
Dim Opening As Double
Dim Credit
Dim Continue As VbMsgBoxResult
Dim TranType As String
Dim Closing As Double
Dim Time As Date
Credit = InputBox("Enter Credit Amount", "Credit Function")
If IsNumeric(Credit) = False Then
Continue = MsgBox("Incorrect Value Entered", , "Credit Function")
ElseIf IsNumeric(Credit) = True Then
Opening = Range("D80000").End(xlUp).Value
Range("A80000").End(xlUp).Offset(1, 0).Value = Opening
Range("B80000").End(xlUp).Offset(1, 0).Value = Credit
Closing = Opening - Credit
Range("D80000").End(xlUp).Offset(1, 0).Value = Closing
TranType = "Out"
Range("C80000").End(xlUp).Offset(1, 0).Value = TranType
Time = Date
Range("E80000").End(xlUp).Offset(1, 0).Value = Time
transaction.TextBox3 = Credit
transaction.TextBox1 = Closing
transaction.TextBox4.Text = Format(Date, "dd/mm/yyyy")
End If
End Sub
Thanks in advance for any help.
-
Feb 26th, 2012, 08:50 AM
#2
Hyperactive Member
Re: if statement problem
Where is the code you have a problem with? All I see is your calculation and entering it into cells.
It is very beneficial if you use the "Code" or "VBCode" brackets for your code (found at the top of the message box you typed all the info in).
-
Feb 26th, 2012, 12:46 PM
#3
Thread Starter
New Member
Re: if statement problem
The code i have used is below, it has two issues one is the one i mentioned above where the vbyesno argument returns only the first statement i enter.
The second problem is if the amount credited does not take the user into a negative figure then it wont update the spreadsheet.
Dim Opening As Double
Dim Credit
Dim Continue As VbMsgBoxResult
Dim TranType As String
Dim Closing As Double
Dim Time As Date
Credit = InputBox("Enter Credit Amount", "Credit Function")
If IsNumeric(Credit) = False Then
Continue = MsgBox("Incorrect Value Entered", , "Credit Function")
ElseIf IsNumeric(Credit) = True Then
Closing = Opening - Credit
If Closing < 0 Then
Continue = MsgBox("this will take you to your overdraft" & vbCrLf & "Continue?", vbYesNo)
If Continue = vbYes Then
Opening = Range("D80000").End(xlUp).Value
Range("A80000").End(xlUp).Offset(1, 0).Value = Opening
Range("B80000").End(xlUp).Offset(1, 0).Value = Credit
Closing = Opening - Credit
Range("D80000").End(xlUp).Offset(1, 0).Value = Closing
TranType = "Out"
Range("C80000").End(xlUp).Offset(1, 0).Value = TranType
Time = Date
Range("E80000").End(xlUp).Offset(1, 0).Value = Time
transaction.TextBox3 = Credit
transaction.TextBox1 = Closing
transaction.TextBox4 = Time
ElseIf Continue = vbNo Then End
End If
End If
End If
End Sub
-
Feb 26th, 2012, 01:31 PM
#4
Thread Starter
New Member
Re: if statement problem
Private Sub CommandButton3_Click()
Dim Opening As Double
Dim Credit
Dim Continue As VbMsgBoxResult
Dim TranType As String
Dim Closing As Double
Dim Time As Date
Credit = InputBox("Enter Credit Amount", "Credit Function")
If IsNumeric(Credit) = False Then
Continue = MsgBox("Incorrect Value Entered", , "Credit Function")
ElseIf IsNumeric(Credit) = True Then
Closing = Opening - Credit
If Closing < 0 Then
Continue = MsgBox("this will take you to your overdraft" & vbCrLf & "Continue?", vbYesNo)
If Continue = vbYes Then
Opening = Range("D80000").End(xlUp).Value
Range("A80000").End(xlUp).Offset(1, 0).Value = Opening
Range("B80000").End(xlUp).Offset(1, 0).Value = Credit
Closing = Opening - Credit
Range("D80000").End(xlUp).Offset(1, 0).Value = Closing
TranType = "Out"
Range("C80000").End(xlUp).Offset(1, 0).Value = TranType
Time = Date
Range("E80000").End(xlUp).Offset(1, 0).Value = Time
transaction.TextBox3 = Credit
transaction.TextBox1 = Closing
transaction.TextBox4 = Time
ElseIf Continue = vbNo Then End
End If
End If
End If
End Sub
This is the code it also does not update the spreadhseet if closing is >0
-
Feb 26th, 2012, 07:16 PM
#5
Hyperactive Member
Re: Excel 2007 IF statement Issue
Your code coppied into numbered code tags...
vb Code:
Private Sub CommandButton3_Click()
Dim Opening As Double
Dim Credit
Dim Continue As VbMsgBoxResult
Dim TranType As String
Dim Closing As Double
Dim Time As Date
Credit = InputBox("Enter Credit Amount", "Credit Function")
If IsNumeric(Credit) = False Then
Continue = MsgBox("Incorrect Value Entered", , "Credit Function")
ElseIf IsNumeric(Credit) = True Then
Closing = Opening - Credit
If Closing < 0 Then
Continue = MsgBox("this will take you to your overdraft" & vbCrLf & "Continue?", vbYesNo)
If Continue = vbYes Then
Opening = Range("D80000").End(xlUp).Value
Range("A80000").End(xlUp).Offset(1, 0).Value = Opening
Range("B80000").End(xlUp).Offset(1, 0).Value = Credit
Closing = Opening - Credit
Range("D80000").End(xlUp).Offset(1, 0).Value = Closing
TranType = "Out"
Range("C80000").End(xlUp).Offset(1, 0).Value = TranType
Time = Date
Range("E80000").End(xlUp).Offset(1, 0).Value = Time
transaction.TextBox3 = Credit
transaction.TextBox1 = Closing
transaction.TextBox4 = Time
ElseIf Continue = vbNo Then End
End If
End If
End If
End Sub
-
Feb 26th, 2012, 07:21 PM
#6
Hyperactive Member
Re: Excel 2007 IF statement Issue
Well, for starters, look at line 18. You do not set the value of your "Opening" variable until line 26, so on line 18 it is always going to = 0.
-
Feb 27th, 2012, 03:17 PM
#7
Re: Excel 2007 IF statement Issue
would also be an issue, as time is a vb function as well as date
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 27th, 2012, 03:40 PM
#8
Hyperactive Member
Re: Excel 2007 IF statement Issue
Good point Pete, I didn't catch that one
-
Mar 3rd, 2012, 10:10 PM
#9
Thread Starter
New Member
Re: Excel 2007 IF statement Issue
I fixed the problem here's the code
vb Code:
Private Sub CommandButton3_Click() Dim Opening As Double Dim Credit Dim Continue As VbMsgBoxResult Dim con As VbMsgBoxResult Dim TranType As String Dim Closing As Double Dim Time As Date Credit = InputBox("Enter Credit Amount", "Credit Function") If IsNumeric(Credit) = False Then Continue = msgbox("Incorrect Value Entered", , "Credit Function") ElseIf IsNumeric(Credit) = True Then Opening = Range("D80000").End(xlUp).value Closing = Opening - Credit If Closing < 0 Then con = msgbox("overdraft", vbYesNo, "") End If If con = vbYes Then Opening = Range("D80000").End(xlUp).value Range("A80000").End(xlUp).Offset(1, 0).value = Opening Range("B80000").End(xlUp).Offset(1, 0).value = Credit Closing = Opening - Credit Range("D80000").End(xlUp).Offset(1, 0).value = Closing TranType = "Out" Range("C80000").End(xlUp).Offset(1, 0).value = TranType Time = Date Range("E80000").End(xlUp).Offset(1, 0).value = Time transaction.TextBox3 = Credit transaction.TextBox1 = Closing transaction.TextBox4 = Format(Date, "dd-mm-yyyy") Continue = msgbox("Your Transaction Has Been Succeful" & vbCrLf & "" & vbCrLf & "Opening Balance: £" & Opening & vbCrLf & "" & vbCrLf & "Credit Amount: £" & Credit & vbCrLf & "" & vbCrLf & "Closing Balance: £" & Closing, vbOKOnly, "Transaction Succesful") End If If con = vbNo Then End End If If Closing > 0 Then Opening = Range("D80000").End(xlUp).value Range("A80000").End(xlUp).Offset(1, 0).value = Opening Range("B80000").End(xlUp).Offset(1, 0).value = Credit Closing = Opening - Credit Range("D80000").End(xlUp).Offset(1, 0).value = Closing TranType = "Out" Range("C80000").End(xlUp).Offset(1, 0).value = TranType Time = Date Range("E80000").End(xlUp).Offset(1, 0).value = Time transaction.TextBox3 = Credit transaction.TextBox1 = Closing transaction.TextBox4 = Format(Date, "dd-mm-yyyy") Continue = msgbox("Your Transaction Has Been Succeful" & vbCrLf & "" & vbCrLf & "Opening Balance: £" & Opening & vbCrLf & "" & vbCrLf & "Credit Amount: £" & Credit & vbCrLf & "" & vbCrLf & "Closing Balance: £" & Closing, vbOKOnly, "Transaction Succesful") End If End Sub
Last edited by vbnoo; Mar 3rd, 2012 at 10:13 PM.
-
Mar 3rd, 2012, 10:12 PM
#10
Thread Starter
New Member
Re: Excel 2007 IF statement Issue
The Time = date issue you mentioned has not been a problem when executing the code.
-
Mar 3rd, 2012, 10:20 PM
#11
Thread Starter
New Member
Re: [RESOLVED] Excel 2007 IF statement Issue
I resolved the if statement execution problem by correctly ending the If statements when they ended instead of closing them all at the end befor the end of the function like i did on my first post.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|