#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
You macro works for me - it replaces the dropdown with the string "I made a selection". See attached.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
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). |
#4
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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.
|
#6
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Quote:
|
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
#10
|
||||
|
||||
Did you actually exit the formfield (eg by pressing the Tab key) after making your selection?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Quote:
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. |
#12
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#13
|
||||
|
||||
Quote:
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 [Fmr MS MVP - Word] |
#14
|
|||
|
|||
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?
|
#15
|
||||
|
||||
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 [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Fire drop down Macro on change | carlosriver24 | Word | 1 | 08-02-2012 07:18 PM |
Macro ? - Checkbox in Word + drop down selection help | dice1976 | Word | 4 | 07-24-2012 11:18 AM |
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 |
Please help me with drop downs in Excel 2010 | mhays | Excel | 6 | 01-16-2012 09:29 AM |