Posts by Justin Doward

    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

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


    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.


    I have used the technique outlined here:

    External Content
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    I used a different method to make a volatile range than is described in the video, I also tested by expanding the list of products to over 6000 and the response is instant.

    The only issue that I know of which might be a problem is that it depends on how recent your version of excel is as to whether the functions are available.

    Let me know how it goes:

    Searchable Drop Down JD edit.xlsx

    I had made a mistake in the above, and I could not get the code to work correctly in the workbook you uploaded for some reason - I think it was the filter you had used.

    If you open this book and press the button on sheet 2 it should give the results I outlined on the other forum.

    If it is working correctly we can work on cleaning up the output formatting to meet needs, as it is the code is not very flexible. If it fails to run entirely it meansyou need to switch on the reference for solver in the visual basic developer window. ALT-F11 should open it and then TOOLS, then REFERENCES and look for SOLVER and make sure it is checked.

    NB: The code in this workbook has a minor change to the previous post on line 68.


    Hi Grace,

    You need to ensure you follow the rules here, so let them know you have the thread on the other forum:…=1#comment2546142_1656880

    I will have another look at the code tomorrow, I ran it but it is not working on your sheet as it did on the example I put together from the pictures you posted. Not sure why.

    Anyone else interested, this is the code that was working to automate solver:

    As indicated in the post on the other forum, I have activated the solver addin and ticked the solver reference (this does not work otherwise).


    Try this code and let me know if it does some of what you are after, if not then I need a better idea of what you are trying to achieve.

    On your sheet1 select A2:A49 and run the code.

    Hi DA,

    There is a minor error in the code as it is starting its comparison for the OUTPUT at A3 but writing to A2, so if you run it twice it rewrites the first line. To correct it, in the data as formatted in your example sheet, you need to change the serials3 to cover from A2:A ...


    The code is for a worksheet_change event so does not need to be linked to a button.

    I may have misinterpretted what you wanted, this works so that as you type the serial number in LIST it updates the OUTPUT but only for that line/row. If you want a macro for a button I will have a look at that soon.

    Hi Daghs,

    Try this code on the "List" worksheet.