Solved: drop down form field re-select

[OTWarrior]

I have a multitude of drop down form fields on a document that will be used by people of different skill levels, so want to make it as easy as possible.

since drop down form fields have a limit of 25 items, i clear the list if the last item is chosen and rebuild it from an array. To make sure the user is aware that it is a new list in the drop down, I want the drop down box to be selected again and to drop down the menu.

I know how to do this if the user "tabs out", but if use the mouse to select anyway else other than the next box, it can look very confusing.

the macro is run on exit, and looks something like this:

********************

Sub filler()
On Error Resume Next
Dim Data3 As Variant
Data3 = Array("info", "main list..")
Dim Data4 As Variant
Data4 = Array("info2", "main list..")
Dim Data5 As Variant
Data5 = Array("info3", "main list..")
Dim LocData As Variant 'default loading list
LocData = Array("Select an option..", "list1..", "list2...", "list3...")

Dim j As Integer, j2 As Integer, j3 As Integer, iProvLoop As Integer
Dim BKMname2 As String
If Selection.FormFields.Count = 1 Then
'No textbox but a check- or listbox
BKMname2 = Selection.FormFields(1).Name
ElseIf Selection.FormFields.Count = 0 And Selection.Bookmarks.Count > 0 Then
BKMname2 = Selection.Bookmarks(Selection.Bookmarks.Count).Name
End If
Dim DefaultList As String
DefaultList = LBound(LocData)
Dim Dropper As String
Dropper = "%{down}"

If ActiveDocument.FormFields(BKMname2).Result = "main list.." Then
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Clear
For iProvLoop = DefaultList To UBound(LocData)
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add LocData(iProvLoop)
Next iProvLoop
ActiveDocument.FormFields(BKMname2).DropDown.Value = 1
SendKeys "{LEFT}"
SendKeys Dropper
Exit Sub
ElseIf ActiveDocument.FormFields(BKMname2).Result = LocData(DefaultList + 3) Then
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Clear
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add "Select list1 option"
For j = LBound(Data5) To UBound(Data5)
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add Data5(j)
Next j
ActiveDocument.FormFields(BKMname2).DropDown.Value = 1
SendKeys "{LEFT}"
SendKeys Dropper
Exit Sub
ElseIf ActiveDocument.FormFields(BKMname2).Result = LocData(DefaultList + 2) Then
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Clear
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add "Select list2 option"
For j2 = LBound(Data4) To UBound(Data4)
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add Data4(j2)
Next j2
ActiveDocument.FormFields(BKMname2).DropDown.Value = 1
SendKeys "{LEFT}"
SendKeys Dropper
Exit Sub
ElseIf ActiveDocument.FormFields(BKMname2).Result = LocData(DefaultList + 1) Then
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Clear
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add "Select list3 option"
For j3 = LBound(Data3) To UBound(Data3)
ActiveDocument.FormFields(BKMname2).DropDown.ListEntries.Add Data3(j3)
Next j3
ActiveDocument.FormFields(BKMname2).DropDown.Value = 1
SendKeys "{LEFT}"
SendKeys Dropper
Exit Sub
Else
Exit Sub
End If

End Sub
********************
as you can see, i am selecting the box again (SendKeys "{LEFT}") then calling the drop down box (SendKeys Dropper). But how would I specifically select the box i have just left?
I have tried :

ActiveDocument.FormFields(BKMname2).Range.Select

Selection.GoTo What:=wdGoToBookmark, Name:=BKMname2

ActiveDocument.Bookmarks(BKMname2).Range.Fields(1).Result.Select

...And none of these seem to select the form field.

What am I doing wrong?

[OTWarrior]

Here is a working example in a document so you can see what I am working on.

[fumei]

1. Please use the VBA tags when posting code.

2. An On Exit macro has NOT left the formfield.
how would I specifically select the box i have just left?
Repeat. You have NOT left it yet.

You can even select it within an On Exit, and it WILL be selected. But as soon as the On Exit terminates...Word continues with what it is supposed to do...go to the next formfield.

Try using an On Entry macro in the next formfield.

I am not totally getting what you are doing though.

[OTWarrior]

1. Please use the VBA tags when posting code.

2. An On Exit macro has NOT left the formfield.Repeat. You have NOT left it yet.

You can even select it within an On Exit, and it WILL be selected. But as soon as the On Exit terminates...Word continues with what it is supposed to do...go to the next formfield.

Try using an On Entry macro in the next formfield.

I am not totally getting what you are doing though.

Sorry about the (lack of) vba tags, only just joined the site so don't know fully the conventions, will remember to use them next time.

If it hasn't left the form field when the macro runs, why does "Sendkeys {LEFT}" reselect the form field (or rather, continues to work) when you press tab, but not when you use the mouse to select elsewhere?

Does the mouse fully exit the macro? or does it override certain commands (selection in this case)

What I am trying to do is reselect the form field I have left (on certain criteria), even when you use the mouse to select any other form field, as that seems to stop the selection code in the onexit macro.

I was given this code

Private mstrFF As String

Public Sub AOnExit()
With GetCurrentFF
If ActiveDocument.FormFields("Dropdown1").Result = "..." Then
MsgBox "You can't leave field"
mstrFF = "Dropdown1"
SendKeys "%{DOWN}"
End If
End With
End Sub

Public Sub AOnEntry()
With GetCurrentFF
Dim strCurrentFF As String
If ActiveDocument.FormFields("Dropdown1").Result = "..." Then
ActiveDocument.FormFields("Dropdown1").Select
mstrFF = vbNullString
End If
End With
End Sub

This works, but it doesn't allow me to dynamically change the name, or to use: ActiveDocument.FormFields(BKMname2).Result = LocData(DefaultList + 3) as the test, so I would be bound by the specific string values in the drop down list.

ActiveDocument.FormFields(BKMname2).Result = "..Next list"

[OTWarrior]

the problem is these 2 lines:
ActiveDocument.FormFields(BKMname).Dropdown.ListEntries.Clear
ActiveDocument.FormFields(BKMname).Dropdown.Value = 1

However, these are crucial to making this work, as I need to remove all items in the dropdown's list and make sure the first option is selected.

Is there another way of removing items from the list?
and how can I set the default without using the line from above?

[OTWarrior]

anyone?

[fumei]

Looking at it.

BTW:

1. use Option Explicit
2. use objects
Dim docFF As FormFields
Set docFF = ActiveDocument.FormFields
Now you can use:docFF(BKMname).DropDown.ListEntries.Clear


Instead of:ActiveDocument.Formfields(BKMname).DropDown.ListEntries.Clear

[OTWarrior]

I have finally done it! just working on increasing the speed it runs, and reducing the flicker that appears when you update the list (I can remove the flicker, but it causes other issues)...so here is the fully working code (no option explicit btw)

Dim BKMName As String
Dim ResultCheck As String
Dim nGenCount As Boolean
Public unHighlighted As Boolean
Public ffSelect As Word.FormField
Public ffSelectList As Word.ListEntries
Public ffSelectDropdown As Word.DropDown
Public HighlightSettings As Word.Range



Public Sub AOnEntry()
Set HighlightSettings = Selection.Range
If Selection.FormFields.Count = 1 Then
'No textbox but a check- or listbox
BKMName = Selection.FormFields(1).Name
ResultCheck = ActiveDocument.FormFields(BKMName).Result
Call ffGenerator
Exit Sub
ElseIf Selection.FormFields.Count = 0 And Selection.Bookmarks.Count > 0 Then
BKMName = Selection.Bookmarks(Selection.Bookmarks.Count).Name
ResultCheck = ActiveDocument.FormFields(BKMName).Result
Call ffGenerator
Exit Sub
Else
MsgBox "TOASTIE!!"
End If
ResultCheck = ffSelect.Result
Call ffGenerator
Exit Sub
End Sub



Public Function ffGenerator()
On Error GoTo ErrerHandel
Set ffSelect = ActiveDocument.FormFields(BKMName)
Set ffSelectDropdown = ffSelect.DropDown
Set ffSelectList = ffSelectDropdown.ListEntries
nGenCount = False

Application.ScreenUpdating = False

Dim Data As Variant
Data = Array("Data5","Data6","Data7","Data8", "..Next List...")
Dim Data2 As Variant
Data2 = Array("Data1","Data2","Data3","Data4", "...Previous List")

Dim i As Integer, i2 As Integer

If ffSelect.Result = "Select an Option" Then
Call unHighlighter
Exit Function

ElseIf ffSelect.Result = "..Next List..." Then

ffSelect.Select
With ffSelectList
.Clear
.Add "Select an Option"
Application.DisplayStatusBar = True
Application.StatusBar = "Please wait while Word rebuild the dropdown list..."
For i2 = LBound(Data2) To UBound(Data2)
.Add Data2(i2)
Next i2
End With
ffSelectDropdown.Value = 1
nGenCount = True

Application.DisplayStatusBar = False
Call Highlighter

DoEvents
SendKeys "%({DOWN})", True

Call ffGenerator
ElseIf ffSelect.Result = "...Previous List" Then


ffSelect.Select
With ffSelectList
.Clear
.Add "Select an Option"
Application.DisplayStatusBar = True
Application.StatusBar = "Please wait while Word rebuild the dropdown list..."
For i = LBound(Data) To UBound(Data)
.Add Data(i)
Next i
End With
ffSelectDropdown.Value = 1
nGenCount = True

Application.DisplayStatusBar = False
Application.ScreenUpdating = True
Call Highlighter

DoEvents
SendKeys "%({DOWN})", True

Call ffGenerator
Else
nGenCount = False
Application.ScreenUpdating = True
Call unHighlighter


End If
Exit Function


ErrerHandel:
MsgBox Err.Description, 64, Err.Number
Exit Function

End Function




Public Sub AOnExit()
Set ffSelect = ActiveDocument.FormFields(BKMName)
If nGenCount = True Then
Call Highlighter
Exit Sub
Else
If ffSelect.Result = "...Previous List" Then
Call ffGenerator
Exit Sub
ElseIf ffSelect.Result = "..Next List..." Then
Call ffGenerator
Exit Sub
Else
Call unHighlighter
Exit Sub
End If

End If
End Sub




Public Function Highlighter()
If ActiveDocument.ProtectionType <> wdNoProtection Then
bprotected = True
ActiveDocument.Unprotect Password:=""
End If

HighlightSettings.HighlightColorIndex = wdYellow
unHighlighted = False

If bprotected = True Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=""
End If
Application.ScreenUpdating = True
End Function




Public Function unHighlighter()
If unHighlighted = True Then Exit Function
If ActiveDocument.ProtectionType <> wdNoProtection Then
bprotected = True
ActiveDocument.Unprotect Password:=""
End If
HighlightSettings.HighlightColorIndex = wdNoHighlight
unHighlighted = True

If bprotected = True Then
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=""
End If
Application.ScreenUpdating = True
End Function


Hopefully this will help out anyone else who wants to use drop down form fields and get around the 25 limit problem.