0

I have an issue with VBA code that I would like to get help with.

I have a code with 3 If-Then Statements. IF TextBox5 = 0; TextBox5 = 1; and TextBox5 > 1 I need to insert an error msg but I don't know what would be the correct location for each of the If-Then Statements. Error msg: "The Leg Doesn't Exist. Please Check The Traveller And Try Again."

Private Sub TextBox5_AfterUpdate()

On Error Resume Next
If TextBox5.Value = 0 And Not IsEmpty(TextBox5.Value) Then

   Set MyRange = ActiveWorkbook.Worksheets("Raw Data").Range("Table_Query_from_Visual654[base_id]")

   For Each c In MyRange
       If c.Value Like UCase(TextBox4.Value) Then
          If Sheet1.Cells(c.Row, 4) = TextBox5.Value Then
            TextBox13.Value = Sheet1.Cells(c.Row, 5)
            TextBox14.Value = Sheet1.Cells(c.Row, 6)
          End If
        End If
   Next
End If

If TextBox5.Value = 1 And Not IsEmpty(TextBox5.Value) Then

   Set MyRange = ActiveWorkbook.Worksheets("Raw Data").Range("Table_Query_from_Visual654[base_id]")

   For Each c In MyRange
      If c.Value Like UCase(TextBox4.Value) Then
         If Sheet1.Cells(c.Row, 4) = 0 Then         ' Set it to 0 if 1 is enetered into TextBox5
            TextBox13.Value = Sheet1.Cells(c.Row, 5)
            TextBox14.Value = Sheet1.Cells(c.Row, 6)
         End If
      End If
   Next
End If

If TextBox5.Value > 1 And Not IsEmpty(TextBox5.Value) Then

   Set MyRange = ActiveWorkbook.Worksheets("Raw Data").Range("Table_Query_from_Visual654[base_id]")

   For Each c In MyRange
      If c.Value Like UCase(TextBox4.Value) Then
         If Sheet1.Cells(c.Row, 4) = TextBox5.Value Then
            TextBox13.Value = Sheet1.Cells(c.Row, 9)
          Else
          TextBox14.Value = ""
          End If
      End If

   Next

End If
Worksheets("Cost Analysis").Range("B1").Value = UCase(TextBox5.Value)

Call TextBox6_AfterUpdate
Call TextBox9_data

End Sub
Johnny Abreu
  • 375
  • 1
  • 16
TCO
  • 61
  • 6
  • 1
    From your brief description of the logic, I'm not sure if you want three different messages or if you want a message unless the value of TextBox5 < 0. (**Note:** Textbox values are usually strings, so not sure how vba handles the comparison to numbers) However, if you want to see some examples on how to do error handling, you can check out this link: https://stackoverflow.com/a/1046222/9259306 -------- Also, `On error resume next` masks every error, so you should consider changing that. – Mistella Jul 05 '18 at 19:45
  • Based on your suggestion, I have replaced On Error Resume Next with On Error GoTo errMyErrorHandler. Then added the following at the bottom. `End If errMyErrorHandler: MsgBox Err.Description, vbExclamation + vbOKCancel, "The Leg Doesn't Exist. Please Check The Traveller And Try Again" Err.Clear Worksheets("Cost Analysis").Range("B1").Value = UCase(TextBox5.Value) Call TextBox6_AfterUpdate Call TextBox9_data End Sub` – TCO Jul 05 '18 at 19:55
  • There is a problem with this. I get this error message even when there is no error. – TCO Jul 05 '18 at 19:56
  • @YuriyBaron *"I get this error message even when there is no error"* - not possible :) – dwirony Jul 05 '18 at 19:57
  • @dwirony think that there is a better way to write these if-then statements. I simply do not know what that is. Right now, the error msg simply executes no matter what value is placed into TextBox5 – TCO Jul 05 '18 at 19:58
  • @YuriyBaron If you're always getting the error message, did you add an `Exit Sub` after your normal code and before the error handler? – Mistella Jul 05 '18 at 20:09
  • So I was able to get it to work but not completely. I have gone back to the original code that I posted in the question and added yet another IF statement at the end. `If TextBox13.Value = "" Then MsgBox "The Leg Doesn't Exist. Please Check The Traveller And Try Again." End If` The issue here is that if there is already something in TextBox13, it will not erase it if the value in the TextBox5 changes. – TCO Jul 06 '18 at 00:37

0 Answers0