Microsoft Office Forums Drop-Downs:  Run Macro on Exit

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-13-2012, 12:18 PM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default Drop-Downs: Run Macro on Exit

It appears that a macro that I have set up to run "on exit" from a drop-down list is not executing. At present, I am simply interested in learning how to get any macro to run on exit. Here is the macro that I have set up to run on exit:



Sub PrintTextonSelection()
ActiveDocument.Unprotect
Dim MyText As String
MyText = "I made a selection"
Selection.TypeText (MyText)
End Sub

To test, I first protected the file (in order to allow use of the drop-down) and then made a selection. Result: the "I made a selection" text was not inserted nor did the document transition to an unprotected state.

I am a complete novice re VBA and macros. Any suggestions welcomed.
Reply With Quote
  #2  
Old 11-13-2012, 02:05 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

You macro works for me - it replaces the dropdown with the string "I made a selection". See attached.
Attached Files
File Type: doc OnExit.doc (45.0 KB, 62 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 11-13-2012, 02:17 PM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
You macro works for me - it replaces the dropdown with the string "I made a selection". See attached.
Thanks, but it does not work for me (I tested with your attachment).

I guess there must be some "setting" that I need to change somewhere. Any thoughts?

I am a little confused - When I examine the properties of the dropdown list from your attachment, there is nothing selected in the "on exit" field. Nor does the name of my macro appear in the list of macros I can select "on exit".

I am basically a complete novice, but at least I believe I grasp the concept that a macro, once defined, has to be "reachable" from the document in which it is invoked. Could my problem have to do with this? Could my macro be correct (which it appears to be since it works for you) but it cannot be "seen" from the file in which I invoke it? That seems unlikely since, in my document at least, the relevant macro name can indeed be selected from the "on exit" field.

By the way I should have stated I am trying this with Word 2003, despite what my profile suggests (I have 2 computers, one with 2003, one with 2010).
Reply With Quote
  #4  
Old 11-13-2012, 02:23 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The issue is most likely that your Office Macro Security settings are too high. Go to Developer|Macro Security and set the level to 'Disable all macros with notification'. Then re-open the document. The 'On Exit' macro should then fire as described.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 11-13-2012, 02:46 PM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
The issue is most likely that your Office Macro Security settings are too high. Go to Developer|Macro Security and set the level to 'Disable all macros with notification'. Then re-open the document. The 'On Exit' macro should then fire as described.
I am running Word 2003, and it does not appear I have access to any 'Disable all macros with notification' setting. However, I tried setting macro security level to the lowest setting and then saving and re-opening as per your suggestion - still no success.
Reply With Quote
  #6  
Old 11-13-2012, 04:20 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

In Word 2003, the equivalent is Tools|Macro|Security > Medium. Do be aware that you need to open the document after chnaging this setting. Also, if you've saved the attachment since downloading it, Word may have deleted the macro because of the previous settings.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 11-14-2012, 08:13 AM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
In Word 2003, the equivalent is Tools|Macro|Security > Medium. Do be aware that you need to open the document after chnaging this setting. Also, if you've saved the attachment since downloading it, Word may have deleted the macro because of the previous settings.
Thanks for all you advice, but still no success.
Reply With Quote
  #8  
Old 11-14-2012, 09:01 AM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default Unprotecting a Document via Macro

As per another thread, I am having all sorts of problems getting a macro to run "on exit" from a drop-down list.

I think there is a fundamental issue that I need some help on. In order for a drop-down list to be "active", the document must be in a protected state. So I ensure my document is in a protected state when the document is provided to a user (who will use the drop-down). Fine.


Now suppose I want the macro to insert text "on exit". Well, presumably I have to unprotect the document to do this. Don't I need to therefore insert this line of VBA code in the macro?:

ActiveDocument.Unprotect

When I do this, I still do not get success - when the user makes a selection, the document remains in a protected state and the text is not entered.
Reply With Quote
  #9  
Old 11-14-2012, 12:55 PM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default

I am really quite mystified. I created a macro that unprotects a document. And I know that it works - I tested by assigning the macro to a key, protecting the document, and then activating the key. Sure enough, the document transitions to an unprotected state.

But when I assign that very same macro to fire "on exit" from a drop-down, it does not work. And I have figured out (through "debugging") that the macro does not fire when the user makes a selection from the drop-down.

Can anyone explain to me what specific event constitutes "exiting" a drop-down? I had assumed that the selection of some item from the list via the mouse constitutes the "exit" event. Is that correct?

Last edited by Andrew H; 11-14-2012 at 02:33 PM.
Reply With Quote
  #10  
Old 11-14-2012, 02:35 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Did you actually exit the formfield (eg by pressing the Tab key) after making your selection?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 11-14-2012, 02:55 PM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Did you actually exit the formfield (eg by pressing the Tab key) after making your selection?
That was the problem. Thanks.

I find it odd that in all the many web pages I read about this, I never encountered an explanation of precisely what constitutes the "exit" event. I am a complete neophyte - I never knew that pressing the tab key constituted exiting the formfield.

And many of the the people who use my document will never think to press the tab key once they make a selection. When one makes a selection in a drop-down list, one need not press the tab key to proceed to, for example, the next drop-down list - one can simply "mouse over".

So it would seem that I might be out of luck - there is no way that the users will be guaranteed to press the tab key to move on to the next field in the document.

That seems like an odd restriction - is perhaps something else I am missing?

Thanks again for your help.
Reply With Quote
  #12  
Old 11-14-2012, 05:50 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

If you have multiple formfields, then any time the use selects one (whether via the tab key or the mouse) they'll exit the previous one, triggering its 'on exit' macro (if it has one).
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 11-14-2012, 06:04 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Andrew H View Post
I am having all sorts of problems getting a macro to run "on exit" from a drop-down list.

I think there is a fundamental issue that I need some help on. In order for a drop-down list to be "active", the document must be in a protected state. So I ensure my document is in a protected state when the document is provided to a user (who will use the drop-down). Fine.


Now suppose I want the macro to insert text "on exit". Well, presumably I have to unprotect the document to do this.
There's a couple of issues to consider here.

First, you don't necessarily need a macro to conditionally display some text in the document. This could be done via field coding, with the formfield's 'calculate on exit' option checked. See, for example: https://www.msofficeforums.com/word-...html#post38948

Second, if the text is to be put into another formfield, or an unprotected Section of the document, there is no need to unprotect the document.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 11-15-2012, 09:52 AM
Andrew H Andrew H is offline Drop-Downs:  Run Macro on Exit Windows 7 32bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Advanced Beginner
Drop-Downs:  Run Macro on Exit
 
Join Date: Nov 2012
Location: Montreal, Quebec
Posts: 51
Andrew H is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
If you have multiple formfields, then any time the use selects one (whether via the tab key or the mouse) they'll exit the previous one, triggering its 'on exit' macro (if it has one).
Great - I did not know this. However, what about the last formfield? How can I get a macro to trigger when the user makes a selection from the last formfield in the document?
Reply With Quote
  #15  
Old 11-15-2012, 01:00 PM
macropod's Avatar
macropod macropod is offline Drop-Downs:  Run Macro on Exit Windows 7 64bit Drop-Downs:  Run Macro on Exit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,525
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

There is no 'on selection' event for a formfield, so unless you can use an on-entry macro, nothing other than the last formfield's content will update until the user exits it.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drop-Downs:  Run Macro on Exit Fire drop down Macro on change carlosriver24 Word 1 08-02-2012 07:18 PM
Drop-Downs:  Run Macro on Exit Macro ? - Checkbox in Word + drop down selection help dice1976 Word 4 07-24-2012 11:18 AM
Drop-Downs:  Run Macro on Exit Drag and drop macro Amanoo PowerPoint 2 06-15-2012 04:01 AM
Drop and drag macro Jamtart PowerPoint 3 04-15-2012 11:05 PM
Drop-Downs:  Run Macro on Exit Please help me with drop downs in Excel 2010 mhays Excel 6 01-16-2012 09:29 AM


All times are GMT -7. The time now is 03:33 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft