Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Menu Form in a macro (Excel 2002)

    The Form from the Data menu is handy for adding to or editing a particular range of data.

    If I record my action to open the form, close the form and then stop the macro. I find that <ActiveSheet.ShowDataForm> has been recorded. If I replay that macro it fails with a:-

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Menu Form in a macro (Excel 2002)

    This is the error message that you get when the activesheet has no form. Are you sure the activesheet has a form.
    It doesn't matter what you select
    If you have more than 1 sheet "selected" the first one is the activesheet.

    You could try explicitly naming the sheet instead of using "activesheet".
    Worksheets("Sheet1").ShowDataForm

    This will pull up the form, no matter what sheet is active (it will allow you to pull the form up without showing the datasheet at all. The datasheet could even be hidden!

    Steve

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Menu Form in a macro (Excel 2002)

    Steve
    Thanks for prompt answer, no luck with your suggestion though. The only dataform the active sheet has is the one produced by Excel itself when, from the Data Menu you choose Form. The appropriate dataform popped up when I recorded the macro, the error message showed when I ran the macro.

    Does this mean that a dataform used in a macro has to be created beforehand and then called by the macro?
    I have very limited experience with Userforms but they don't seem to be appropriate to build a form similar to the Dataform that Excel produces when Data/Form is chosen.

    Tony

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Menu Form in a macro (Excel 2002)

    It will work if the table starts in the upper left corner of the worksheet (cell A1). If it doesn't, you must name the range "Database" (in Insert | Name | Define).

    See XL2000: ShowDataForm Method Fails if Data Cannot Be Found.

  5. #5
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Menu Form in a macro (Excel 2002)

    Thanks Hans,
    naming the range "database" solved the problem, I had given it a name that suited me but not Excel!
    To take it further, is it possible to have the dataform open at a particular record or does it always start with record 1?

    Further still can Visual Basic control the form so that a Criteria can be entered and then Find Next used?
    Again, thanks for the help
    Tony
    Rutland, UK

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Menu Form in a macro (Excel 2002)

    I think you are pretty much stuck with the dataform as excel has it. It is meant to be simple to use so there are no enhancements.

    You can try John Walkenbach's Enhanced dataform. The addin is <img src=/S/free.gif border=0 alt=free width=30 height=15> to download and use, but you must purchase the password if you want to view add modify the VB code behind the dataform.

    For complete cusomization you can also create your own userform that can work exactly as you want for data entry.

    Steve

  7. #7
    Star Lounger
    Join Date
    Jan 2002
    Location
    Melton Mowbray, UK
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Menu Form in a macro (Excel 2002)

    Thanks to all who helped; the John Walkenbach Enhanced Data Form was exactly what I needed.

    Tony

Posting Permissions

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