Results 1 to 11 of 11

Thread: [RESOLVED] Excel 2007 IF statement Issue

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    Resolved [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.

  2. #2
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

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

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    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

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    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

  5. #5
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Re: Excel 2007 IF statement Issue

    Your code coppied into numbered code tags...
    vb Code:
    1. Private Sub CommandButton3_Click()
    2.  
    3.     Dim Opening As Double
    4.     Dim Credit
    5.     Dim Continue As VbMsgBoxResult
    6.     Dim TranType As String
    7.     Dim Closing As Double
    8.     Dim Time As Date
    9.  
    10.     Credit = InputBox("Enter Credit Amount", "Credit Function")
    11.  
    12.     If IsNumeric(Credit) = False Then
    13.         Continue = MsgBox("Incorrect Value Entered", , "Credit Function")
    14.  
    15.  
    16.     ElseIf IsNumeric(Credit) = True Then
    17.  
    18.         Closing = Opening - Credit
    19.  
    20.     If Closing < 0 Then
    21.  
    22.         Continue = MsgBox("this will take you to your overdraft" & vbCrLf & "Continue?", vbYesNo)
    23.  
    24.     If Continue = vbYes Then
    25.  
    26.         Opening = Range("D80000").End(xlUp).Value
    27.         Range("A80000").End(xlUp).Offset(1, 0).Value = Opening
    28.  
    29.         Range("B80000").End(xlUp).Offset(1, 0).Value = Credit
    30.  
    31.         Closing = Opening - Credit
    32.         Range("D80000").End(xlUp).Offset(1, 0).Value = Closing
    33.  
    34.         TranType = "Out"
    35.         Range("C80000").End(xlUp).Offset(1, 0).Value = TranType
    36.  
    37.         Time = Date
    38.         Range("E80000").End(xlUp).Offset(1, 0).Value = Time
    39.  
    40.         transaction.TextBox3 = Credit
    41.         transaction.TextBox1 = Closing
    42.         transaction.TextBox4 = Time
    43.  
    44.     ElseIf Continue = vbNo Then End
    45.  
    46.     End If
    47.     End If
    48.     End If
    49. End Sub

  6. #6
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    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.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel 2007 IF statement Issue

    vb Code:
    1. time = date
    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

  8. #8
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    Re: Excel 2007 IF statement Issue

    Good point Pete, I didn't catch that one

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    Re: Excel 2007 IF statement Issue

    I fixed the problem here's the code

    vb Code:
    1. Private Sub CommandButton3_Click()
    2. Dim Opening As Double
    3. Dim Credit
    4. Dim Continue As VbMsgBoxResult
    5. Dim con As VbMsgBoxResult
    6. Dim TranType As String
    7. Dim Closing As Double
    8. Dim Time As Date
    9.  
    10. Credit = InputBox("Enter Credit Amount", "Credit Function")
    11.  
    12.     If IsNumeric(Credit) = False Then
    13.         Continue = msgbox("Incorrect Value Entered", , "Credit Function")
    14.  
    15.  
    16.     ElseIf IsNumeric(Credit) = True Then
    17.    
    18.         Opening = Range("D80000").End(xlUp).value
    19.             Closing = Opening - Credit
    20.            
    21.                 If Closing < 0 Then
    22.                
    23.                     con = msgbox("overdraft", vbYesNo, "")
    24.                    
    25.                     End If
    26.    
    27.                         If con = vbYes Then
    28.    
    29.                             Opening = Range("D80000").End(xlUp).value
    30.                                 Range("A80000").End(xlUp).Offset(1, 0).value = Opening
    31.  
    32.                             Range("B80000").End(xlUp).Offset(1, 0).value = Credit
    33.  
    34.                             Closing = Opening - Credit
    35.                                 Range("D80000").End(xlUp).Offset(1, 0).value = Closing
    36.  
    37.                             TranType = "Out"
    38.                                 Range("C80000").End(xlUp).Offset(1, 0).value = TranType
    39.  
    40.                             Time = Date
    41.                                 Range("E80000").End(xlUp).Offset(1, 0).value = Time
    42.            
    43.                                 transaction.TextBox3 = Credit
    44.                                 transaction.TextBox1 = Closing
    45.                                 transaction.TextBox4 = Format(Date, "dd-mm-yyyy")
    46.                                 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")
    47.                        
    48.                         End If
    49.        
    50.                         If con = vbNo Then End
    51.                         End If
    52.  
    53.                 If Closing > 0 Then
    54.  
    55.                     Opening = Range("D80000").End(xlUp).value
    56.                          Range("A80000").End(xlUp).Offset(1, 0).value = Opening
    57.  
    58.                     Range("B80000").End(xlUp).Offset(1, 0).value = Credit
    59.  
    60.                     Closing = Opening - Credit
    61.                         Range("D80000").End(xlUp).Offset(1, 0).value = Closing
    62.  
    63.                     TranType = "Out"
    64.                         Range("C80000").End(xlUp).Offset(1, 0).value = TranType
    65.  
    66.                     Time = Date
    67.                         Range("E80000").End(xlUp).Offset(1, 0).value = Time
    68.  
    69.                         transaction.TextBox3 = Credit
    70.                         transaction.TextBox1 = Closing
    71.                         transaction.TextBox4 = Format(Date, "dd-mm-yyyy")
    72.                         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")
    73.  
    74.                 End If
    75. End Sub
    Last edited by vbnoo; Mar 3rd, 2012 at 10:13 PM.

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    Re: Excel 2007 IF statement Issue

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

  11. #11

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    6

    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
  •  



Click Here to Expand Forum to Full Width