[RESOLVED] Excel 2007 IF statement Issue

[vbnoo]

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.

[nO_OnE]

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).

[vbnoo]

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

[vbnoo]

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

[nO_OnE]

Your code coppied into numbered code tags...

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

[nO_OnE]

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.

[westconn1]

time = date

would also be an issue, as time is a vb function as well as date

[nO_OnE]

Good point Pete, I didn't catch that one

[vbnoo]

I fixed the problem here's the 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

[vbnoo]

The Time = date issue you mentioned has not been a problem when executing the code.

[vbnoo]

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.