Trigger Opening a Form

JerryDal · #1 (**permalink**) 01-09-07, 18:07

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:
ABC 12,000
DEF 500

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?

Thanks.
Jerry

MikeTheBike · #2 (**permalink**) 01-10-07, 09:04

Hi Jerry

I am sure there are a number of ways to open a form, but I think it would be useful if you stated what you would like to happen.

For example, you can open the form when the spreadsheet is opened by
putting this is the "ThisWorkbook" module !!

Code:

Private Sub Workbook_Open()
 UserForm1.Show
End Sub

MTB

JerryDal · #3 (**permalink**) 01-10-07, 13:30

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.

Jerry

loquin · #4 (**permalink**) 01-11-07, 00:19

can concurrent users be editing a single excel file?

JerryDal · #5 (**permalink**) 01-11-07, 12:50

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.

HTML Code:

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,
'otherwise exit
...
End Sub

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.

HTML Code:

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
        Exit Sub
    End If
End Sub

Unless anyone has any suggestions or corrections, this data entry question can be considered resolved.

Thanks.
Jerry

Internet Services

Web Development

Digital Marketing

Consumer Tech