Results 1 to 5 of 5

Thread: Data Forms

  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good evening

    I am trying to record a macro to make a data form entry sheet. I have about 15 columns but no matter where I place the cursor when I start it gives the error message below. Can I stop this so that when I click my button the data form comes straight up?
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Set up your data table in such a way that Excel doesn't have to guess at its structure or ask you about it:

    - The first row of the table should contain column headings.
    - The column headings should not take up more than one row.
    - If the row with the column headings is not the first row of the worksheet, the row above it should be blank.
    - The row below the row with the column headings should *not* be entirely blank.
    - Preferably, format the row with the column headings in a way that distinguishes it from the rows below, e.g. by making the text bold.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796470' date='05-Oct-2009 20:36']Set up your data table in such a way that Excel doesn't have to guess at its structure or ask you about it:

    - The first row of the table should contain column headings.
    - The column headings should not take up more than one row.
    - If the row with the column headings is not the first row of the worksheet, the row above it should be blank.
    - The row below the row with the column headings should *not* be entirely blank.
    - Preferably, format the row with the column headings in a way that distinguishes it from the rows below, e.g. by making the text bold.[/quote]

    Hi Hans

    Thanks for that, it seems as though placing numbers 1 through 28 under the column headings did what I wanted but I now have another problem (not sure if I should post as a new topic)

    I have recorded a macro 'CompleteInvoice' with the following steps, Open data form, click 'new' (so that the numbers I have entered don't show) and then close.

    I have assigned the Macro to a button but it is now giving me an error, when I debug this is what I get this which highlights ActiveSheet.ShowDataForm

    Sub CompleteInvoice()
    '
    ' CompleteInvoice Macro
    ' Macro recorded 06/10/2009 by steve
    '
    ' Keyboard Shortcut: Ctrl+o
    '
    ActiveSheet.ShowDataForm
    End Sub

    ??
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Without seeing the workbook I have no idea why the error occurs.

    Keep in mind that the macro recorder only records opening the data form. It does not record any actions that you perform while the data form is open, such as clicking New, and there is no VBA equivalent of performing actions in the data form. You could simulate pressing Alt+W (the keyboard shortcut for the New button):

    Code:
      SendKeys "%w"
      ActiveSheet.ShowDataForm

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If your data does not start in the range A1:B2 and is not named Database, then you will get that error.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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