Print envelope using selection from listbox

  • I'll try this again. Everything works fine as i stated before but where the problem starts is if you select multiple records in the listbox to print envelopes for. The code will see all the selections made, (I verified this with the debugger) but when it actually comes time to print the envelopes, it will only print the last record selected in the listbox. "Last" meaning the last alphabetically. I'm not sure on this one. Does the code need to print to the oDoc for each selection found? If so, that could turn into a nightmarish issue. ive included the code for the print function. Can someone take a look and see if they can figure out an answer to this issue? it has to print all selections, not just the last one.


    Thanks again for any and all help!!

  • ok, updated sample attached. The part that is controlled by that sheet is working fine. All the data sheet holds at this point is the values from the settings page to build the rAddr. I will probably eliminate that sheet eventually and just use variables or something similar but for now, it is what it is.


    Have a look, it should work for you now. Print only one record, it'll work just fine. Try to print more than one, it will only print the last alphabetical record you choose.

  • Sorry, I had a day out yesterday. Try this


  • Code
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2. Sheets("Master data").Range("E4").Value = (250 - (Sheets("Master data").Range("T4").Value - Sheets("Master data").Range("L4").Value))
    3. End Sub

    Which sheet is the above code in? In fact attach a small example file.

  • No worries Roy, i just have 2 weeks to complete this project and this was holding me up. as far as the code above for the "Master Data" sheet, it was just something i was helping another user with and it may have got sent to you by accident. just disregard that code. ill try your fix and see if it works for me. i tried to move the "Next iX" and the "end with" to include the code for the envelopes before but got an error. ill try your fix and see if it works as needed.


    again, thanks for the help. we need wizards like you for this stuff. ive learned a ton for you and hope to learn a lot more!


    Cheers!

  • Now I remember what the problem was after i put your fix in place. By putting the "Next iX" and "End With" at the end of the code, it doesn't matter what is checked in the listbox. It spits out an envelope for EVERY record. This particular project right now has 55,695 records in it. I have to be able to have it print only the records that are selected in the listbox.

  • This works for me


  • Are you attempting to print more than 1? This doesn't work for me. If i select more than 1 in the listbox, it will only print the last selection in alphabetical order. If its working for you than what could possibly be the issue why it wont work for me? If i select 1, it prints 1, if I select 5, it prints 1, if i select 10, it prints 1... and the one it prints is ALWAYS the last in alphabetical order?? I'm extremely confused now.

  • I have added a message box for testing purposes.


    I selected two, three and four items ran the code and the msgbox only showed for each selected item.


    Are you using a different workbook because there is no Data sheet in the example?

  • Thanks Roy... With your example, it got me on the right path. Its working perfect now.


    The old code:

    The fix? EVERYTHING had to be inside of the with statement EXCEPT the oWord.Quit False statement otherwise the oWord statement call would quit prematurely. I figured it had to be something simple. Correcting the call order was all that was needed. Thanks again for your help.


    The working code: