[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.
Re: Excel 2007 IF statement Issue
Quote:
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
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.
Re: Excel 2007 IF statement Issue
would also be an issue, as time is a vb function as well as date
Re: Excel 2007 IF statement Issue
Good point Pete, I didn't catch that one
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
Re: Excel 2007 IF statement Issue
The Time = date issue you mentioned has not been a problem when executing the code.
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.