Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ShowDataForm (excel 2000)

    I'm trying to use visual basic to do what I thought would be a very simple task, bring up a form to enter information in a worksheet.
    The first heading is in a5, when in excel I just highlight a5 thu to the end of the table and click on forms. so this macro should do the same thing.

    Sub form3()
    '
    Range("a5").Activate
    Range("A5:U53").Select
    Worksheets(1).ShowDataForm
    End Sub

    but this sets up a form as if I had highlighted A1.
    please help I'm at a loss.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShowDataForm (excel 2000)

    Recorded using the macro recorder <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 16/04/2002 by sprduser
    '

    Range("A5").Select
    ActiveSheet.ShowDataForm
    End Sub


    HTH

    Peter

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShowDataForm (excel 2000)

    Your problem probably lies in the fact that you don't explicitly tell XL what sheet you are talking about. This should work:

    Thisworkbook..Worksheets("YourSheetsName").Activat e
    Activesheet.[A5].Select
    Activesheet.ShowDataForm

    If you omit the sheet designation, it is assumed you meant the active sheet. It may very well be (in your original code) that you were on sheet(2) whilst the code shows the dataform for sheet(1)!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShowDataForm (excel 2000)

    Thanks for your efforts, but I still have the same problem, the right sheet is active, but the wrong part of the sheet is used for the form.
    to explain further, I've attached a sample file, my headings are in range(a5:k5) and the data area is in range(a6:k18), I want a form using these headings.
    doing it manually in XL I get what I want,
    but in VB I get "A1" as the heading and the data area is "a2:a4"



    Regards, Glenn
    Attached Files Attached Files

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShowDataForm (excel 2000)

    Indeed XL behaves as you stated (sorry for not believing you <ws>).

    This does work:

    Range("A5:K13").Select
    Application.CommandBars.FindControl(, 860).Execute
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Mar 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShowDataForm (excel 2000)

    I tried and it works perfectly.
    Many thanks, I never would have figured out the solution myself, (afraid even after seeing it, I don't entirely understand it.).
    It seems you are activating the command by executing the XL menu item (,860). if so, is there a list in the help of what id applies to each menu item.

    regards, Glenn

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ShowDataForm (excel 2000)

    Hi Glenn,

    <<It seems you are activating the command by executing the XL menu item (,860).>>

    Spot on.

    << if so, is there a list in the help of what id applies to each menu item>>

    Download xlmenufundict.zip from

    http://www.bmsltd.ie/mvp

    Edited Mar 13th 2004 to update link
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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