Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data forms and macros (Excel 2000)

    Hello Everyone,

    I have a spreadsheet and I would like to create a macro that selects a range of headings and then create a data form.
    I can create the macro - OK, however when I run it I get an error. The code has the following lines:

    Range("b4:v12").Select
    data.ShowDataForm


    The error appears on the second line.

    Any ideas on the code.

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  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 forms and macros (Excel 2000)

    Is data the name of the worksheet that has the dataform?

    Use either:
    activesheet.ShowDataForm
    or
    Sheets("data").ShowDataForm

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data forms and macros (Excel 2000)

    Hi Steve,

    Yes data is the name of the sheet, I previously tried your suggestion and I still got the error on the .showdataforms
    command.
    It does seem to recognise the selection.

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  4. #4
    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 forms and macros (Excel 2000)

    What is the error message?

    I will speculate, and ASSUME (correct me if I am wrong) that the "errror" is actually an ALERT message: something to the effect that excel can not determine which row contains column labels.

    This shows up when he column labels (headers) and all the data are text. Excel is not sure if the top row is the label row or just the first set of data. The default response is "yes, the top row is the header"

    To get around this, turn off the display alerts before the command and then turn it back on after.

    <pre> Application.DisplayAlerts = False
    Sheets("data").ShowDataForm
    Application.DisplayAlerts = True
    </pre>


    If this is not the error, I will need a little more to go on.

    One other comment, instead of using a defined range, just do:

    <pre> Range("B4").select
    Application.DisplayAlerts = False
    Sheets("data").ShowDataForm
    Application.DisplayAlerts = True
    </pre>


    In your way, the first time will you run the macro will work fine and you can add more data, but the next time, the new data will NOT be in the mentioned range so when the macro runs on subsequent times, the "dataform" will only use the referenced range and you will be unable to add NEW data.

    Selecting only one cell will have excel expand the range to accomodate ALL the date in the "currentregion", will be used for the dataform so will "grow" when new data is added.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data forms and macros (Excel 2000)

    Hi Steve,

    Thank you for you suggestions [img]/forums/images/smilies/smile.gif[/img] I have tried the code however I still get the error messages.
    Your previous posting "hit the nail on the head". I have posted a document that outline the error
    messages in sequence.



    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]
    Attached Files Attached Files

  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 forms and macros (Excel 2000)

    Based on the error message, your "dataset" has at least one merged cell in it and this must be removed for the Dataform to work

    To remove the merged cells:
    Select the range of interest.
    Format - cells - alignment tab
    make sure the "merge cells" box is UNCHECKED

    now the macro should run ok
    The First message "alert" should be fixed by the code with the DisplayAlerts I gave you earlier.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Melbourne, Australia
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data forms and macros (Excel 2000)

    Hi Steve,

    Brilliant it worked, no alerts, the data form appeared. [img]/forums/images/smilies/smile.gif[/img] [img]/forums/images/smilies/smile.gif[/img]
    One more thing [img]/forums/images/smilies/smile.gif[/img], I only want the cells I select to appear on the
    data form, and currently it is taking every single cell on my worksheet and putting
    it as an entry on the form.

    Does that happen to you??

    I have tried selecting only the cells i want b4:v4, select the entire row b4, and used your suggestions,
    however it still grabs every cell????

    Any ideas?

    Thanks Kindly,

    Kerrie [img]/forums/images/smilies/smile.gif[/img]

  8. #8
    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 forms and macros (Excel 2000)

    Try adding blank columns to "fence in the area". I assume that the area below is clear so that you can add more data.

    I am not sure what the problem is. When I try in XL97 to select a subset in a dataform it seems to work fine.

    Steve

Posting Permissions

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