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.


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

  • Hi Victor,


    I think this is all you need, otherwise I will need to clarify with you.


    This now catches the error if there is an error with the worksheet name, but ignores it and ends the sub if the error is due to a match fail.

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

    Files

    • V10.xlsm

      (535.15 kB, downloaded 24 times, last: )
  • Hi Victor,


    I added a TRUE FALSE check for the reset, so now if you press reset you do not activate the worksheet_change event for the H column.


    I chaned the code for the form so that when you select an item the resulting value in the B column is checked against the selected worksheet and if it does not exist you get a message pop-up.


    Is that what you were after?


    V10 JDedit.xlsm

  • 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

    Files

    • Excel.xlsm

      (561.78 kB, downloaded 25 times, last: )
  • Hi Victor,


    I changed the listbox code to allow for a blank and to test if G9 is a date value, I have not tested thoroughly so let me know if it errors. If you intend to use the forms across more sheets I will recommend a differnt approach to the code using Case.

  • 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:


    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.


    Code
    1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    2. If Not Intersect(Target, Range("C14:C25")) Is Nothing Then
    3. UserForm1.Show
    4. If Err.Number <> 0 Then Range("C" & Target.row).MergeArea.ClearContents
    5. End If
    6. 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

    Files

    • Excel.xlsm

      (544.77 kB, downloaded 24 times, last: )
  • Hi Victor,


    I am not sure what you are trying to do in your code for the doubleclick, there is no waythe mergearea should activate. It will only enter the if statement if you doubleclick on the range and then it will show the form, there is no reason for an error to occur so the error number will never not be 0 (unless there is something I am missing).


    If you want to discuss what you are trying to get to happen here PM me, if it is working as you expect all good.


    Justin

  • 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 Victor, I had a look in context and understand how it works now, nice job!


    The alternative is to catch the error prior to exiting the form so the user has the chance to correct the selection without having to double click again, eg:


  • 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 ?

  • Hi Victor,


    The code below currently returns to the text box and highlights the text so you can either start typing to overtype the existing search or make a new selection using the same search. If you want to delete the text in the text box delete the .selstart and .sellength lines and remove the comment symbol to so the .value line works instead.

    I also put the option of a beep sounding rather than the msgbox appearing, again delete the beep line if that is not what you want.

    Then there is the option for a second textbox hidden below the listbox, if you expand the size of the form in developer and put a second textbox in there then shrink the form so it is hidden again the code will change the height (see attachment).


    We probably need to move from this thread, it is getting far from the original question. If you have a share drive for the file you could send me a link to the drive. Use the messaging rather than this thread.


    V10 JDedit2.xlsm

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