Solved: Holding DropDown Menu Selection Using Select Case

[junkmark]

I am creating a form in which the user makes a selection from a drop down menu with 5 options in it. I am using an exit macro to control text in an adjacent cell. Thus, the user selection in the dropdown menu determines what text will be output.

I have tried an If... Then, but after looking around on here, went with a Select Case. I used some examples I found here and I am able to output the correct text at the correct time and in the correct location.

The PROBLEM is that the user's selection from the drop down menu does not hold. In other words, the user selects option two, then tabs to the next form field, and in doing so the appropriate text is displayed, but the dropdown menu RESETS to the first option.

I have tried a million things and I am still pretty new to this all, so please help (and please be gentle if i am doing something stupid). Thank you so much!

Here is what I have:


Sub SpecialProgramOption()

Set Doc = ActiveDocument
If Doc.ProtectionType <> wdNoProtection Then Doc.Unprotect

Dim ProgramOptions()
Dim var
Dim i As Integer
Dim POValue As Integer
ProgramOptions = Array("<Special Programs Option>", "Hessen Global", "Formal Internship", "IUSP Marburg", "European Studies, Frankfurt")

POValue = ActiveDocument.FormFields("DropDown7").DropDown.Value

Select Case ActiveDocument.FormFields("DropDown7").DropDown.ListEntries.Item(POValue).Name

Case "<Special Programs Option>"
With ActiveDocument.Tables(2).Cell(Row:=7, Column:=2).Range
.Delete
End With
With ActiveDocument
.Protect wdAllowOnlyFormFields
End With

Case "Hessen Global"
With ActiveDocument.Tables(2).Cell(Row:=7, Column:=2).Range
.Delete
.InsertAfter Text:="(combined summer course work and internship)"
End With
With ActiveDocument
.Protect wdAllowOnlyFormFields
End With

Case "Formal Internship"
With ActiveDocument.Tables(2).Cell(Row:=7, Column:=2).Range
.Delete
.InsertAfter Text:="(please follow the Hessen Networks Link below, click on 'Incomings' and link to the 'Hessen/Wisconsin Internship Program' for additional information and application procedures: link goes here)"
End With
With ActiveDocument
.Protect wdAllowOnlyFormFields
End With

Case "IUSP Marburg"
With ActiveDocument.Tables(2).Cell(Row:=7, Column:=2).Range
.Delete
.InsertAfter Text:="(International Undergraduate Study program, Semester or Academic Year)"
End With
With ActiveDocument
.Protect wdAllowOnlyFormFields
End With

Case "European Studies, Frankfurt"
With ActiveDocument.Tables(2).Cell(Row:=7, Column:=2).Range
.Delete
.InsertAfter Text:="(4 weeks, early summer)"
End With
With ActiveDocument
.Protect wdAllowOnlyFormFields
End With

End Select

End Sub



My brain hurts...

[fumei]

PLEASE! Use the underscore character to break up your code lines.

On my monitor the code window stretches FIVE TIMES the visible width.

Normally, I have decided I will not respond to any painfully wide threads like this.

However, this one is very easy.

When you are resetting the protection you are resetting the values. As you noticed.
With ActiveDocument
.Protect wdAllowOnlyFormFields, NoReSet:=True
End With should fix this.

The default is to reset to the original items.

[fumei]

Other comments.

1. You declare and Set a document variable ("Doc")...but never use it! Why bother if you never use it?

2. You declare a array variable (ProgramOptions), and even populate it...but you never use it! Why bother if you never use it?

3. For each of the case statements you use a Cell range. That is fine, but as it is the SAME range for each one, declare a range object and use it. There is no need to state it for each Case.

4. You also repeat the Protection instruction for each Case. No need. Use it once at the end. Here is your code, using underscore characters so it fits into the code window here.
Sub SpecialProgramOption()
Dim Doc As Document
Dim r As Range

Set Doc = ActiveDocument
Set r = Doc.Tables(2).Cell(7, 2).Range
If Doc.ProtectionType <> wdNoProtection Then Doc.Unprotect

Select Case Doc.FormFields("DropDown7").Result
Case "<Special Programs Option>"
r.Delete
Case "Hessen Global"
With r
.Delete
.InsertAfter Text:="(combined summer course " & _
"work and internship)"
End With
Case "Formal Internship"
With r
.Delete
.InsertAfter Text:="(please follow the Hessen " & _
"Networks Link below, click on 'Incomings' and " & _
"link to the 'Hessen/Wisconsin Internship Program' " & _
"for additional information and application " & _
"procedures: link goes here)"
End With
Case "IUSP Marburg"
With r
.Delete
.InsertAfter Text:="(International Undergraduate Study " & _
"program, Semester or Academic Year)"
End With
Case "European Studies, Frankfurt"
With r
.Delete
.InsertAfter Text:="(4 weeks, early summer)"
End With
End Select
Doc.Protect wdAllowOnlyFormFields, NoReSet:=True
Set r = Nothing
Set Doc = Nothing
End SubI removed the array, as it was not being used. I removed the extraneous protection instructions. More importantly, it uses a Range object (r) to handle the Cell range, rather that declaring it every time.

And of course, it uses NoReset:=True.

[junkmark]

Thank you so much for both the quick fix and for the complete re-vamp of the code! This will be a HUGE help. I apologize for not using line-breaks, but my brain was so fried by the time I posted this that I wasn't even thinking of line-width. Any way, I will be sure and use them in all future posts. Again, thank you for the help, and thank you for making an exception to your rule.

Cheers,
Mark