Retrieve information from different workbook with userform

  • Hello,


    I am working on a project to make the sales staff's job easier. I have created a Quote form that we use for all our quoting. Now I am working on an invoice form where we can just pull the information from a quote into the invoice. The quotes are all saved in one folder with name format Qt-1, Qt-2....


    I found a VBA code that pulls the information from the quote into the invoice. Works great, however I would like to use that code in a userform where the sales staff can enter the quote number and the magic happens with a click of a button. In the code I have the file path and the workbook I want to retrieve. The file path and name (TestInvoice) will always be the same, the only variable will be the document name. That variable number I would like to be entered in a textbox in the userform.


    How can this code be modified to work in a userform?


  • Re: Retrieve information from different workbook with userform


    Quote from gijsmo;764640

    If you only want the user to enter the quote number, one alternative is to put a "button" on the worksheet and use an InputBox to grab the quote number.


    See example attached.
    ozgrid.com/forum/core/index.php?attachment/68184/


    Love that idea! I tried your code, when I enter "Qt-1" it tells me it's an invalid number, if I simply enter "1" nothing happens.


    Thank you so much for your suggestion and help.

  • Re: Retrieve information from different workbook with userform


    Quote from gijsmo;764640

    If you only want the user to enter the quote number, one alternative is to put a "button" on the worksheet and use an InputBox to grab the quote number.


    See example attached.
    ozgrid.com/forum/core/index.php?attachment/68184/


    Love that idea! I tried your code, when I enter "Qt-1" it tells me it's an invalid number, if I simply enter "1" nothing happens.


    Thank you so much for your suggestion and help.

  • Re: Retrieve information from different workbook with userform


    The InputBox is only expecting a number (1,2,3 etc). The "Qt-" is automatically appended in the modified version of your original code:


    Set wb = Workbooks.Open("C:\Users\Username\Desktop\TestInvoice\Qt-" & sQNo & ".xlsx", True, True)


    So if you enter anything other than a number, it will fail inbuilt InputBox validation.
    If you enter a decimal point, it will be treated as an invalid number by the code.

  • Re: Retrieve information from different workbook with userform


    Quote from gijsmo;764692

    The InputBox is only expecting a number (1,2,3 etc). The "Qt-" is automatically appended in the modified version of your original code:


    Set wb = Workbooks.Open("C:\Users\Username\Desktop\TestInvoice\Qt-" & sQNo & ".xlsx", True, True)


    So if you enter anything other than a number, it will fail inbuilt InputBox validation.
    If you enter a decimal point, it will be treated as an invalid number by the code.


    I am still having trouble with this code. When I enter a number into the InputBox nothing happens. What am I doing wrong?