How to stop the main sub if a module gives an error.

Hello,

I come to you looking for a solution that is probably simple for others, but I really can't figure it out.

Here is the resume:

I have the code below that works great, but I want it to stop completely and go to MyEnd if XShts (which is in module 1) turnes out to be an error.

If xShts doesn't return an error, then both of them should do what they do.

But if xShts gives an error, I want to see MyErr on the screen and the 1st code should stop there (The code for xShts already does that).

If you have any solutions, can you please respond with the updated code for this issue ? I'm not good enough to do it myself based on instructions.

Thank you in advance.

Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H14:H25")) Is Nothing Then
Dim x As Long, MyBool As Boolean
Dim ws2 As Worksheet: Set ws2 = Sheet2
Dim ws3 As Worksheet: Set ws3 = Sheet3
On Error Resume Next
x = Application.Match(Range("B" & Target.Row), ws2.Range("B4:B500"), 0)
MyBool = False
If IsNumeric(x) Then
For i = 7 To Columns.Count
If ws2.Cells(1, i).EntireColumn.Hidden = False Then
ws2.Range("G" & x + 3).Offset(0, i - 7) = ws2.Range("G" & x + 3).Offset(0, i - 7).Value2 + CStr(Target.Value)
If MyBool = False Then Call XShts(Target.Row)
MyBool = True
GoTo MyEnd
End If
Next i
End If
End If

MyEnd:
End Sub

Sub XShts(TgtRW As Long)
GoTo MyStart
MyErr:
MsgBox "Error: The street may not be defined or the street may not have this item code"
GoTo MyEnd
MyStart:
On Error GoTo MyErr
Dim ws3 As Worksheet: Set ws3 = Sheet3
If Len(ws3.Range("G9")) Then
Dim wsX As Worksheet: Set wsX = Worksheets(Range("G9").Value2)
Dim MyXRW As Long
MyXRW = Application.Match(Range("B" & TgtRW), wsX.Range("B6:B500"), 0) + 5
If IsNumeric(MyXRW) Then wsX.Range("F" & MyXRW) = wsX.Range("F" & MyXRW) + CStr(ws3.Range("H" & TgtRW))
End If
MyEnd:
End Sub

I have an ideea, maybe it is easier.

If the error of XShts would show up right after it recognisez that the value in B &tgt.row doesn't match the value from B6:B500, instead of showing up after I add a value in column H, then this would work for me too.

Hello Justin,

Nice to see you again.

The code you added doesn't do what I need it to do. Let me explain.

I'll add the excel file here to be easier.

This is what the code from module 1 does now:

Let's say ws3.G9= name of wsX so that the condition is met.

After I doubleclick anywhere on ws3.Range(C14:C25) and select an item from the list, the value in ws3.Range(B14:B25) changes accordingly.

Then, If I add a value in ws3.Range(H14:H25) [it will be in the same row], the code from module 1 starts working and if there are no errors, it does what it has to do, but if there are errors ( no matching found), it goes to MyErr.

Here is an accurate example of what I need it to do instead:

Let's say ws3.G9= name of wsX so that the condition is met

After I doubleclick anywhere on ws3.Range(C14:C25) and select an item from the list, the value in ws3.Range(B14:B25) changes accordingly.

After the value of ws3.Range(B14:B25) shows up, match it with the values from wsX.Range(B:B) and if the match is true, then the code from module 1 should do what it has to do next, but if it doesn't match with any value from wsX.Range(B:B), then go to MyErr.

Basically, I want the module 1 to go to MyErr right after the match if the match is false, instead of going to MyErr after I put a value in ws3.Range(H14:H25) (as it does now).

That way, I know in advance that I should not add any value in ws3.Range(H14:H25) because MyErr is true.

Thank you so much for your involvement in this Excel file.

Hello Justin,

Yes, this is what I wanted. Thank you for the update of the reset button too. It works great.

There is only 1 issue with the new code and that issue is in image 1.

I need to be able to doubleclick in cells C14:C25 and pick an item from the Userform1 pop-up list, even when in G9 is nothing. ( That's why the data validation in G9 starts with 1 empty row).

The error from image 1, shows up only when G9 is empty. The code works perfectly when in G9 is the name of a sheet.

Thank you so much and sorry for the late reply.

Ok, I've found a 2nd problem. I used the same Userform1 for ws4 and since Useform1 is now updated to do that check, it is no longer good for ws4 (which doesn't need it).

I've tried to make a copy of the old Userform1 (which is Userform2 now) but it doesn't work like before. Can you take a look at it, please ?

Please use this excel file instead because it's the updated one.

Thank you

Hello Justin,

You are right, I can use the userform1 for both ws3 and ws4, now that you updated it to allow a blank in G9.

I've made a small modification to your code.

By that, I mean that I removed the check for the date in G9, and I let only the vbNullString. I don't need it to check the date.

So now, in ws3 it matches the name of the sheet from G9 and will also let me select from the listbox if G9 is empty.

As for ws4, I let the G9 empty on purpose so that I can select from the listbox at anytime, without getting an error. (I moved the date in H9)

Doing it like this, I can use the same userform1 for both ws3 and ws4.

Thank you for your help. The thread is now solved with this code:

Option Explicit
Option Compare Text 'ignores capitals during search
Private Sub ListBox1_Click()
Dim x As Long
If Range("G9").Value2 = vbNullString Then GoTo PasteMe
Dim wsX As Worksheet: Set wsX = Worksheets(Range("G9").Value2)
ActiveCell.Value2 = UserForm1.ListBox1.Value
On Error Resume Next
x = Application.Match(ActiveCell.Offset(0, -1).Value, wsX.Range("B6:B500"), 0)
If Err.Number <> 0 Then MsgBox "The street you selected doesn't have this item"
PasteMe:
ActiveCell.Value2 = UserForm1.ListBox1.Value
Unload Me
End Sub
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
Dim ws As Worksheet: Set ws = Sheet2
Dim MyArr, x As Long
UserForm1.ListBox1.Clear
MyArr = Application.Transpose(ws.Range("C4:C" & ws.Cells(Rows.Count, 3).End(xlUp).Row))
For x = LBound(MyArr) To UBound(MyArr)
    If InStr(MyArr(x), UserForm1.TextBox1.Text) Then UserForm1.ListBox1.AddItem MyArr(x)
Next x
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_Activate()
Dim ws As Worksheet: Set ws = Sheet2
Dim MyArr As Variant
MyArr = Application.Transpose(ws.Range("C4:C" & ws.Cells(Rows.Count, 3).End(xlUp).Row))
UserForm1.ListBox1.List = MyArr
Erase MyArr
End Sub

Also, I've added this bit of code (row 4) to this part below and I've chosen inside the datavalidation of C14:C25 to not see the error it gives me after the code clears the contents. I think it works fine like this, but if you have a better ideea of how to do it, I'll do as you say.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C14:C25")) Is Nothing Then
    UserForm1.Show
If Err.Number <> 0 Then Range("C" & Target.row).MergeArea.ClearContents
End If
End Sub

I've added the updated excel file again if you want to take a look at what I said above.

All credits to Justin. Thank you

Hi Justin,

No, the doubleclick doesn't show an error on it's own.

The error results after I add the row 4 to it.

"Errnumber <> 0 " is from the Userform1. Basically, when I added the row 4 to the doubleclick code I said the following:

If Userform1 returns MsgBox "The street you selected doesn't have this item", then clear the contents of the Range("C" & Target.row).MergeArea.

The Mergearea is added because the Column C is merged with the column D, and otherwise it would have not cleared the contents of it.

I did this because I wanted the following to happen:

If the Userform1 has Errnumber <> 0 , then the MsgBox "The street you selected doesn't have this item" shows up and when I click ok on this msgbox to close it, I want the contents of the Range("C" & Target.row) to clear itself automatically.

The row 4 of the doubleclick code does exactly that, but then when the Range("C" & Target.row) is empty, I get an error from the data validation list of that cell.

To fix that, I disabled the error showing up in data validation.

Do you have a better solution to what I did ?

Thank you

Hi Justin,

Sorry for the late reply again.

I see what you did there with the code. It's a good ideea, but is there a way to also clear the textboxt after I click Ok on the error msgbox ?

Please look at the image 1. What I mean by that is:

If the userform1 shows this msgbox, when I click ok, I want the userform to clear the textbox and to look like in image 2 so that I can type new text imediately without doing anything else.

Because if I have to click ok on the msgbox and then to go manually to the textbox, select what I typed there and delete it, and then to input new text in the textbox, it's easier to just doubleclick again with the other version of this.

Do you know how to do this ?

Hello Justin,

Thx for the new code. It works great. I chose the solution with the msgbox 1 appearing and then clearing the contents of the textbox when I click ok.

Thank you for all your ideas and work.

You are right, you solved this thread a long time ago so I will mark it as solved and I will text you in chat in the future.

You did amazing work here.