Adding Data to a Form (VBA/Excel/2003)

Topic

New Reply

Some time ago I worked on some code that allows an Excel user to print a label containing a name and 3 lines of address from a workbook. The workbook uses a VLOOKUP to retrieve the details from another Excel file and display them in 4 rows of the current workbook by typing in a ID number that becomes the Lookup Value.

Through a second form, the code then allows the user to selectively print a label by giving options to the next available label position on a sheet of labels.

The requirement now is to print a label without the details being recorded in the current workbook.

I have determined that I will need at three forms.

1. A first form for the options to either print from the current workbook using VLOOKUP to fill the four rows and then to print the label from the results of the VLOOKUP or to print a label from an ID that is typed into the form directly. The second option would need to have a textbox to type the ID into because it would not appear in the current workbook as it does at present.

2. A second form that displays the results of the chosen ID as Name and Address (because they can’t be displayed in the current workbook).

3. A third form would be where the user chooses the next label position.

Currently a second form is used to determine the label position but I imagine this would become the third form when the second option is chosen (to print a label without the information appearing in the current workbook).

How can I get the first form to use the ID number to search a separate Excel file and lookup a corresponding name and address, and then display it in a second form for confirmation?

To make more sense of what I am trying to achieve I have posted a diagram with the forms I will need to develop.

Reply | Quote

Replies

You could store the information in a hidden sheet, using the VLOOKUP formulas that you already have. Or in a temporary sheet that is discarded after printing the label.
Another option would be to open the other workbook, look up the information (you can use Application.WorksheetFunction.VLookup in code but it only works with open workbooks), and store the results in global variables

Reply | Quote

Thanks Hans – but so that there is nothing ‘extra’ appearing (or hidden) in the current open workbook, I did not wish to create a temp worksheet but use a VBA form to display the results of the external Excel file to populate the second form in my original post.

Is it possible to paste that data into a label by double clicking the preferred label location? You may recall that you (more than) helped me with the original code that enabled the selection of the label position for which I am grateful.

In the end, when they choose the second option to print a label via the ID, I would like to have users just see a confirmation (via a form) before they printed their label. So the next question would be is it possible to pass the value in a form’s textbox to a label directly or must I do that by placing the values back into cells in the open Excel workbook?

Thanks for any insight, Leigh

Reply | Quote

You can look up the data and display them in a form without placing them on a sheet, but in order to print a label, the data must be on a sheet – Excel can only print sheets.

I don’t know what code you are using now (I found an old thread about label placement by you from February 2005, but that was for Word, not Excel).

Reply | Quote

Hi Hans – from a more thorough search of the Lounge I can reveal the code probably came from another source… (This was the one I was thinking of – Post: 141,597)

But I have no idea where the code came from other than it works a treat!

I went back over what you said in your first reply and will follow that path in part.

Users will still be able to select the first option (to print a label from the current workbook) and this uses exactly what you suggested, a hidden worksheet that is deleted after printing.

When they choose the second option, they will type in an ID away from the work area and some adjacent cells will be populated with a VLOOKUP formula and the results will then be printed in the same way as the first option. Finally the information that was added to the worksheet in the current workbook will be deleted and the label will be printed.

Thanks again for your suggestions.

Reply | Quote