A Excel worksheet on a network drive will be used by others for data entry. There are six worksheets and each person entering data will have their own worksheet. Cells in column H are to contain lists of product codes and volumes. Example:
My task is to display a list of 16 product codes, the person will type in all volumes that apply, and from that I will fill in the appropriate cell, including line breaks between products, with the code and volume information for those product codes that have volumes.
I am assuming a form is the best way to collect the information. Question: Is there any way to trigger displaying the form other than having the user active a cell in the row of data and then click a command button at the top to open the form for data entry? Is there a better solution?
Each row represents a prospective client. Column H will contain the list of products, which is a set of codes, and the estimated annual volume for each product.
There are 30 columns on the worsheet, so the data entry person will be filling out more than column H, and that is why the form dedicated to filling out column H should only be visible at the time the column H cell will be populated.
Maybe using a control key sequence to run the macro and open the form is the preferred way as opposed to putting a command button at the top of the worksheet.
Although I work with VBA, I have never used a form for data entry in Excel before, so if anyone knows what should be used (list box or combo box?) I could use the advice. The form will display a list of product codes and to the right of those codes, a column to enter volumes.
can concurrent users be editing a single excel file?
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
The six individuals who will be editing the Excel workbook on a shared drive will have to try later if they find that one of them has the file open.
For the question about entering data from a form for one of the columns, I have decided to use labels and text boxes on the form to list product codes and allow only numeric values in the text boxes. I had asked if this could be done with a list box or combo box. I also learned that there is a doubleclick event for a worksheet, so I will be using that to open the form rather than a command button or a control key sequence. If the current cell already has product codes and volumes, these volumes will be displayed next to the corresponding labeled product code on the form. The form has buttons for each item to clear the volume, and a button to clear all volumes. When the form is completed, those products with formatted volumes will written to the cell in wrapped text format, for example: ABC 1,000 DEF 250.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'open the data entry form if active cell is in a data row (there must be a customer name in column B) and the correct column has been doubleclicked,
Below is sample code used for each text box on the form, which will block any character being entered into the text box other than the characters 0 1 2 3 4 5 6 7 8 9.
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 48 Or KeyAscii > 57 Then
'exit because non-numeric key was pressed
KeyAscii = 0
Cancel = True
Unless anyone has any suggestions or corrections, this data entry question can be considered resolved.