+ Reply to Thread
Results 1 to 15 of 15

Automatically populate form dropdowns in word with macro

  1. #1
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Automatically populate form dropdowns in word with macro

    Automatically populate form dropdowns in word with macro
    I am trying to automatically populate multiple dropdown form boxes in MS Word 2007. For example, I have 60 offices that have a drop down next to each office name. In the dropdowns, my option is either "Blank", "Add" or "Delete".

    Now, I have a separate dropdown located at the top that will give a user the option to select all the offices which will automatically fill in the rest of the dropdowns.

    Here is the code that I have to populate the 60 offices:

    Please Login or Register  to view this content.

    PROBLEM: When I test the macro, I select the "Add" option from the Select all Offices, but I have to click an additional dropdown in order for the macro to work.

    I want this to work automatically and eliminate a click.

    PLEASE HELP!!!!
    Last edited by Cutter; 07-31-2012 at 08:06 PM. Reason: Added code tags

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Automatically populate form dropdowns in word with macro

    @ carlosriver24

    Welcome to the forum. Now that your thread has been moved to the appropriate area you should soon receive the help you want.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

  3. #3
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Lightbulb Re: Automatically populate form dropdowns in word with macro

    Thanks for the welcome info Cutter. I look forward to discussing with others in hopes to solving my dilemma!

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Automatically populate form dropdowns in word with macro

    An 'On Exit' formfield macro requires you to exit the formfield before the macro will run. It doesn't specifically require you to select another formfield, but that's usually what results in the formfield being exited. Simply pressing the tab key should be enough; naturally, though, that will take you to the next formfield.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Automatically populate form dropdowns in word with macro

    Macropod, thanks for your response. My macro does work 'On Exit'; however, I would like to eliminate using the tab button. I know this will fire the macro, but is there any way that I can insert an IF function to trigger the macro once the drop-down is changed.

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Automatically populate form dropdowns in word with macro

    The short answer is no. As I said: "An 'On Exit' formfield macro requires you to exit the formfield before the macro will run". If that wasn't the case, it wouldn't be an 'On Exit' formfield property...

  7. #7
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Automatically populate form dropdowns in word with macro

    Thanks for clarifying Paul. Now is there any way I can fill in those drop downs automatically without using a macro?

  8. #8
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Automatically populate form dropdowns in word with macro

    No. However, if the other fields you're referring to always have the same value as the one you're now using the on-exit macro for, you could instead insert cross-references to the 'Office2' bookmark and not bother having either the on-exit macro or dropdowns at those other locations. Similarly, even if the other fields you're referring to always have a value that is determined by the dropdown's value, you could instead insert IF fields that test the cross-references to the 'Office2' bookmark and vary the output accordingly, again not bothering with either the on-exit macro or dropdowns at those other locations. In either case, all you need to do is to check the 'Office2' formfield's 'calculate on exit' property. See attached.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Automatically populate form dropdowns in word with macro

    Paul,

    Yes, the fields options are the same as the drop down with the 'Office2' bookmark. My apologies, but I am not too familiar with cross references IF functions in VBA. I did select the calculate on exit option for my drop down; however, I did not see a change when I ran the macro. Please see the attached file as my example and please do not hesitate to consult.

    Kind regards,
    Carlos
    Attached Files Attached Files

  10. #10
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Automatically populate form dropdowns in word with macro

    If you look at the document I posted, you'll find that there is no vba code in it. As I said, you don't need a macro for what it does. It's all done with field coding. To see the field coding, open the document, unprotect it, then use Alt-F9 to toggle the field code display on/off.

  11. #11
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Automatically populate form dropdowns in word with macro

    Paul,

    I tried this option, but unfortunately it is not working for me.. Please advise.

    Carlos

  12. #12
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: Automatically populate form dropdowns in word with macro

    Other than 'try again', what would you have me advise?

    ---------- Post added at 12:20 PM ---------- Previous post was at 11:21 AM ----------

    Cross-posted at: http://www.msofficeforums.com/word/1...ro-change.html
    Please read Forum Rule 8!!!

  13. #13
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Automatically populate form dropdowns in word with macro

    Thanks macropod

    Hello carlosriver24, and welcome to the forum.

    Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

    I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  14. #14
    Registered User
    Join Date
    07-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Automatically populate form dropdowns in word with macro

    Oops, I clearly did not read the handbook. Here are a few links where the same question is asked:

    http://stackoverflow.com/questions/1...ord-with-macro

    http://www.msofficeforums.com/word/1...ro-change.html

    @Paul - Thanks for your help. I was able to find a way to fire the macro using the 'onexit' function and without moving away from the dropdown.

    Thanks again!

  15. #15
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Automatically populate form dropdowns in word with macro

    @ carlosriver24

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1