Posts by mycomputerguy-w

    ok.... here is the sample. the combobox im attempting to fill is on the settings userform. I'm thinking the pause will be needed no matter the printer. i know some printers are smart enough to know an envelope will come from the manual feed but the printer i have, for example, needs to have a dialog box that asks for the envelope even if it is already in the feeder. its just a matter of clicking the "OK" button on the dialog. see what you think can be done.


    Thanks again.

    Well Roy, it works, sort of.... i got everything to print out and to print in the right places. Here is the problem. If I use the debug and step the code through manually, it will print just fine. If I use the actual print button, it will not print at all. I've come to the conclusion that the problem lies at the very end of the code but I'm not sure how to fix it. I'm guessing that I need to pause the procedure long enough for the events to complete then, run the very last line to quit the Sub. It seems that It Is exiting before the sub can pop a dialog box to prompt the user to insert the envelope into the manual feed tray. What would be the fastest, easiest way to pause this sub to wait for user input or to allow a dialog box to appear?


    I also have one other question, is there a simple way to populate a combobox with a list of the users installed printers?

    It looks like you may have already fixed it. ill try the updated code and let you know the result.


    EDIT 2: It no longer errors out but instead just does nothing. It pauses like its going to send the document to the printer but then just dies. I think I also may have figured out the oDoc. envelope line.

    Got an issue Roy,


    Not sure what the second "Next iX" is for but its throwing a "Next without for" error. If I comment it out, it still won't complete and go to the printer.


    Also, I'm not sure how to format it but in the

    Code
     oDoc.Envelope.PrintOut , sAddr, , , , , , , "Size 12"

    line (after the 3rd comma) i can see where i can designate this to be the address or return address but I've never used this procedure before so I'm lost as to how to use it. Can you have a look and see if you can point me in the right direction?

    OK Roy, we're getting there! lol... as you can see, I've laid it out as it needs to be seen on the envelope. I know there has to be a better way instead of calling ".List" for each item but the vbCR is needed I'm sure to put everything in the correct format. So if there is a better way to build that "sAddr =", I'm open for suggestions. Nevertheless, what is now printing on the msgbox is exactly what I need to print on the envelope. My next question though would be, I need to check to see which check box is checked. If it is the "Print Absentee Ballot" check box that is checked then I would need this block to print on a size 12 envelope in a smaller font in the return address area. If the "Print Absentee Envelope " check box is checked, then I need this block to print in the main TO: area on a size 14 envelope. I hope that makes sense.


    Thanks again for the help and reply!


    I just reread your questions and see that I missed one. The first row of the table may be empty only because the "Add" button may have been clicked. The way it is right now, an empty entry can be put in and also, that entry goes in at the top rather than the bottom. I haven't set it up yet to stop an empty entry from happening. That will be one of the things I know I need to fix.

    Hello Roy, to answer your questions with a simple answer, nothing is set in stone so if you have better ideas, please, by all means, show me. I'm still learning how to build these things with VBA and have learned SO MUCH from all of you so I encourage and welcome any changes or suggestions. And yes, the intent was to use the sAddr to select the row. If I could simply print the envelope from excel without using word, I'd rather do that but I didn't think that was possible. I'm sure I could "build the form" to print it but because this may be usable by more than my current customer, I figured being able to use the "Size" object would be better in the long run in case a different size envelope is used. I'm also thinking I can make it print "Form Letters" in the future. It's a fairly simple program that doesn't do a whole lot, yet. I'm leaving some room for expansion and different features. Any and all suggestions and changes are welcome, all I ask is for you to "somewhat explain" what you change so I know why and what I'm looking at. Thank you!

    Hello excel gurus,


    Today, I'm in need of some code tweaking to print out envelopes based on the selection from a list box. I have created code to do this but as is my luck, it doesn't seem to want to work. As you'll see in the code, I'm trying to call the word.application / word.document procedure. The user will select as few as 1 line in the listbox to as many as all lines to print envelopes. I will actually need to do this with 2 different size envelopes but that will be 2 separate procedures. If I can one to work, I'm confident I can tweak it to work for the other. This code below uses a "Size 12" envelope and the second would have a "Size 14" envelope. Which procedure (envelope size ) that is used will be determined by which check box is marked as "True". I will eventually, before shipping, enable or disable one or the other of the check boxes based on the selection. Below you will find the code I created. I will also upload a small sample book for testing. I will do my best to answer any and all questions regarding my needs and As always, thank you for the help and I look forward to reading your replies.


    Good Afternoon All. I have a Userform That has multiple textboxes, comboboxes, and labels. what I'm attempting to do for this part is to look at a sheet Value, 2 comboboxes and a text box. what id like to make it do in plain terms is: "If combobox1 says "Small" AND combobox2 says "Wash", get the value of cell E1 from the sheet named "DATA", and calculate it with the input of textbox1. so if the condition are met, "Small" and "Wash" then $30 X 1 =$30. Respectively me.cbsize, me.cbservice, E1 .value, me.tbQty, Me.lblPrice1.


    Hope it all makes sense. Thanks for the great help in advance.

    Hello Guys, below is code that I've built that inserts a row or rows depending on the selection from the user. This works ok but is lacking a few things to make it perfect.
    1st issue: Using the code below, the rows get inserted either above or below the ACTIVE cell. Id like it to just find the last used cell in column "A" above the totals row and do the insert there. (last row needs to stay the last row by just moving down, there is no data in column A on the totals row)
    2nd issue: When the code does the copy, paste, I'm not getting the formulas or the borders in the inserted rows. Id like it to copy just the format and the formulas into the rows pasted.


    Any assistance is GREATLY appreciated



    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    DATA

    [/td]


    [td]

    DATA

    [/td]


    [td]

    DATA

    [/td]


    [/tr]


    [tr]


    [td]

    DATA

    [/td]


    [td]

    DATA

    [/td]


    [td]

    DATA

    [/td]


    [/tr]


    [tr]


    [td]

    INSERT ROW

    [/td]


    [td]

    INSERT ROW

    [/td]


    [td]

    INSERT ROW

    [/td]


    [/tr]


    [tr]


    [td]

    NO DATA

    [/td]


    [td]

    SUBTOTALS

    [/td]


    [td]

    TOTALS

    [/td]


    [/tr]


    [/TABLE]


    Hope this helps and makes sense

    Re: VBA for textbox date to show correctly on sheet


    Thanks for the reply Davesexcel....... I see what you are saying and here is the issue. The user can, and most of the time will, enter the dates with the slashes. The issue pops up when the user forgets the slashes. A date picker would be ok too but time is a factor here and a date picker is somewhat slow and cumbersome. which is why I'm looking for a somewhat simple line or two of code to make sure it gets entered into the cell correctly formatted.

    Second question for you gurus....
    is there a simple VBA code line I can use to count the number of blank rows below the last entry and if there is less than 2 blank rows, add 1 row?


    The reason and method behind this: I have a sheet that uses a userform to collect data, right now at row 1, I have headers, and row 25 I have subtotals, totals and headers. What id like to happen is for the VBA to check how many empty rows exist below the last entry and the bottom headers. if there are less than 2 empty rows, id like it to add a row, THEN enter the text from the userform.


    many of you have taught me some amazing things that can be done with excel and I am beyond grateful, this is just another request to dip into your vast mind.


    Keep up the good work!


    Thanks in advance

    Have 2 questions todays guys, I know this is probably a simple one.


    in my vba code I have a line that reads:

    Code
    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Format(Me.tb3.Value, "MM/DD/YY")


    Using a textbox on a userform, the user enters 092517 in the textbox, when the text is transferred to the sheet it comes out as 04/19/1953. Is there a way I can make this entered text stay as a date as entered : in this case, 09/25/17?


    Thanks again guys! keep up the great work!

    Re: msgbox result to either select offset cell or cancel sub


    Thank You Carim,


    I had to modify the code slightly to make it work correctly for what I needed but you did set me in the right direction..


    Thanks again!


    Re: msgbox result to either select offset cell or cancel sub


    No, there is not a predefined range. I suppose there could be. One reason I use active cell is because I have most of the cells on the sheet locked and protected. By using activecell the selection itself is controlled along with the cell that will be selected if OK is clicked. For example, if cell A5 is a locked cell, Cell A4 is empty and selected, the msgbox pops and says that an empty cell has been selected, when the user clicks OK, the active selection will jump over A5 and select A6. This part and all other parts work fine. The problem is when the user clicks cancel, it STILL advances to A6. all I want it to do is ...... Nothing. Click Cancel, it just stays on the empty cell. (maybe they need to fill the empty cell) As far as using Target, I'm thinking in this case, either one will accomplish the same result. The major problem that I'm trying to address is the end if the file. If Cell A2255 is the last entry on the sheet, and the user happens to click A2256, the macro goes into a loop because A2256 to A???? are empty and will pop the msgbox with no way to stop the loop. That is what I'm trying to do, simply stop the loop.

    OK Guru's, what did I do wrong here? I have a macro that all I'm wanting it to do is to check for an empty cell to the right of the active cell, if that cell is, in fact, empty then pop a msgbox telling the user that they have selected an empty row. If the user clicks "OK" then the active cell becomes the next row below. Here is where the problem is, If the user selects "cancel" the macro is supposed to just exit and do nothing but right now, it also moves the user to the next row below. This would not be a problem but IF the user goes to the end of the file and clicks below the last entry in a file( 1st empty cell at the end of the file), the macro goes into an infinite loop and the user is unable to exit the sub without pressing ctrl+break.



    So where is the problem?