[RESOLVED] Excel VBA: ShowDataForm not working-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] Excel VBA: ShowDataForm not working

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Resolved [RESOLVED] Excel VBA: ShowDataForm not working

    Ok, so I would like to STOP needing to press the DataForm button on my quick access toolbar and would like to call that action via macro and command button (because it would be larger and easier to access than the smaller icon at the top of the page)...

    Here's my issue:
    In my example table
    Name:  ex1.jpg
Views: 2538
Size:  33.7 KB

    I run this macro (which was created via "record macro")
    Code:
    Sub Macro1()
    '
    ' Pulls up Data Form for Selected Table
    '
        Range("D6").Select
        ActiveSheet.ShowDataForm
        Range("I14").Select
    End Sub
    But I get this "Run Time Error 1004"
    Name:  ex2.jpg
Views: 2461
Size:  20.3 KB

    ... can someone explain why this doesn't work, or what I need to manipulate in order to make it run? Suggestions? Work-arounds or bypasses?

    Your help is greatly appreciated.

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Excel VBA: ShowDataForm not working

    If you would have done the bare minimum of research, you would have found your answer. I never have used this method and the first item in the search results for "ShowDataForm" from both Google and Bing provide the answer.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Re: Excel VBA: ShowDataForm not working

    Thanks for the answer TnTinMN, however, your Google and Bing searches FAILED to provide ME the ANSWER that I was looking for; no need to be smug and high-brow with your commentary, especially when you didn't pay one bit of attention to my post... I made it pretty simple with pictures so that I wouldn't get your type of response;

    My requested information was NOT: "How to create a userform", and place code which redirects information into cells... I want to use the DataForm that automatically pops up (without the need for userform creation) when the Dataform button is pressed in the Quick Access Toolbar.

    I'm not sure if you're versed enough in Excel to understand what I'm saying, hopefully that doesn't rub you the wrong way.

    But you can follow these simple steps in order to comprehend what I'm saying... and while you're at it, when you figure out the answer and are humbled enough to share that information with me I'll gladly say thank you.

    --> Create a table
    --> Go to your quick access toolbar and add "Forms" to it
    --> select one of the cells in your table
    --> hit the forms button in the quick access toolbar
    ----> you'll notice a form pops up... amazing huh? and you didn't have to CREATE the userform
    --> use the macro recorder to pull up that form
    ----> run the code
    ------> get the error
    --------> FIGURE IT OUT
    --> Let me and the rest of the world know how smart you are by informing our small minds on how it can be executed.

    Lastly, ~ Thank you for your due diligence ~

  4. #4
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Excel VBA: ShowDataForm not working

    Quote Originally Posted by IGPOD View Post
    Thanks for the answer TnTinMN, however, your Google and Bing searches FAILED to provide ME the ANSWER that I was looking for; no need to be smug and high-brow with your commentary, especially when you didn't pay one bit of attention to my post... I made it pretty simple with pictures so that I wouldn't get your type of response;

    My requested information was NOT: "How to create a userform", and place code which redirects information into cells... I want to use the DataForm that automatically pops up (without the need for userform creation) when the Dataform button is pressed in the Quick Access Toolbar.

    I'm not sure if you're versed enough in Excel to understand what I'm saying, hopefully that doesn't rub you the wrong way.
    I'll concede the point that the order of search results may differ for different people and that the first result you received may have been "How to create a userform", but it proves my point that you did not bother to search for the error message you received.

    Since you have chosen to throw a childish temper tantrum and attempted to make look like a bad guy here, I'll back out of this thread and leave you to your own devices.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Re: Excel VBA: ShowDataForm not working

    LOL... I did about 45 minutes of research trying to find the answer to this... and there were no examples that I could find. If you'd rather concede to your own notions rather than help others learn what you know, then why exactly are you a member of this platform?

    Seems like a waste of intellectual capacity if you're not sharing what you know on a platform designed for people like you (supposedly) to share what you know so that we all can learn from one another...


    Based on the 137 views of this thread, I'd contest that others are running across the same issues with this quick access toolbar macro question.
    Last edited by si_the_geek; Jan 6th, 2015 at 02:23 AM. Reason: removed insults

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,414

    Re: Excel VBA: ShowDataForm not working

    I followed your steps:

    --> Create a table
    --> Go to your quick access toolbar and add "Forms" to it
    --> select one of the cells in your table
    --> hit the forms button in the quick access toolbar
    ----> you'll notice a form pops up... amazing huh? and you didn't have to CREATE the userform
    --> use the macro recorder to pull up that form
    ----> run the code
    ------> get the error
    Got no error.

    (Excel 2013)

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Re: Excel VBA: ShowDataForm not working

    vbfbryce,

    I've encountered this problem in the past with not just myself but with others as well. My work computer is running Microsoft 2007 so a portion of the problem could reside in the difference between versions. I did nail down the problem and I'm about to follow this up with the answer, but quick question for you: which Excel version are you operating on?

    Standby for my answer.

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,414

    Re: Excel VBA: ShowDataForm not working

    2013

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Resolved Re: Excel VBA: ShowDataForm not working

    Here you go world

    Name:  Capture12.jpg
Views: 2328
Size:  21.0 KB

    Here's what I had to do: Change the named range to "Database"
    Name:  Capture13.jpg
Views: 2336
Size:  25.1 KB

    And also in the code:



    And, it worked
    Last edited by si_the_geek; Jan 6th, 2015 at 02:28 AM. Reason: removed insults

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Re: Excel VBA: ShowDataForm not working

    vbfbryce,

    That's what I suspected. I think earlier versions of excel lack the ability to recognize selections within a database as references to databases and therefore blasts users with Run Time Error 1004.

    Being that it works in 2013 without the need to name the range "Database" means the minds at Microsoft most likely fixed the problem.
    Last edited by si_the_geek; Jan 6th, 2015 at 02:28 AM. Reason: removed insults

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Feb 2014
    Posts
    64

    Re: Excel VBA: ShowDataForm not working

    In closing, for anybody experiencing the Run-Time Error 1004 when trying to pull the Data Form using the method:
    Code:
    activesheet.showdataform
    If you are using an earlier version of excel (Prior to 2013; but 2010 has not been tested for this issue yet, I'm currently using 2007) you will need name your table (Select your table, goto formulas tab and define name) as "Database" and then change the reference in your VBA code as well; you can either change the reference to a selection within the dataset, or change the reference to the dataset.

    This code will work:
    Code:
    Sub Macro2()
    
        Range("Database").Select
        ActiveSheet.ShowDataForm
    
    End Sub
    
    OR
    
    Sub Macro2()
    
        Range("[Any cell within the database]").select
        ActiveSheet.ShowDataForm
        
    End Sub
    Last edited by si_the_geek; Jan 6th, 2015 at 02:28 AM. Reason: removed insults

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    39,280

    Re: [RESOLVED] Excel VBA: ShowDataForm not working

    IGPOD, while you may not like what another member posted, responding the way you did is out of order. I have removed several chunks of what you posted (including parts that could be useful to others with a similar problem, because you had written insults on the images).

    Acting in an abusive manner towards others is not acceptable on this site, do not do it again.

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.