Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Trigger Opening a Form

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    can concurrent users be editing a single excel file?
    Lou
    使大吃一惊
    "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


  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •