VBA Dropdown change list Entries automatically

gmayor · #1 09-12-2014, 02:20 AM

OK, it is confirmed a form field and the document is unprotected, so the following will handle that. You can re-activate the commented out list section to change the vessel names.

This also revealed a minor bug in my batch process when run in Word 2010 that has now been fixed in version 2.3.

Code:

Function Ddown(oDoc As Document) As Boolean
Dim oFF As FormField
    On Error GoTo Err_Handler
    For Each oFF In oDoc.FormFields
        If oFF.name = "Dropdown1" Then
            With oFF
                .name = "drpVessel"
                'With .Dropdown.ListEntries
                '    .Clear
                '    .Add "Item 1"
                '    .Add "Item 2"
                '    .Add "Item 3"
                '    .Add "Item 4"
                'End With
            End With
            Exit For
        End If
    Next oFF
    Ddown = True
lbl_Exit:
    Exit Function
Err_Handler:
    Ddown = False
    Resume lbl_Exit
End Function

QA_Compliance_Advisor · #2 09-12-2014, 04:29 AM

Quote:

Originally Posted by gmayor

OK, it is confirmed a form field and the document is unprotected, so the following will handle that. You can re-activate the commented out list section to change the vessel names.

This also revealed a minor bug in my batch process when run in Word 2010 that has now been fixed in version 2.3.

Code:

Function Ddown(oDoc As Document) As Boolean
Dim oFF As FormField
    On Error GoTo Err_Handler
    For Each oFF In oDoc.FormFields
        If oFF.name = "Dropdown1" Then
            With oFF
                .name = "drpVessel"
                'With .Dropdown.ListEntries
                '    .Clear
                '    .Add "Item 1"
                '    .Add "Item 2"
                '    .Add "Item 3"
                '    .Add "Item 4"
                'End With
            End With
            Exit For
        End If
    Next oFF
    Ddown = True
lbl_Exit:
    Exit Function
Err_Handler:
    Ddown = False
    Resume lbl_Exit
End Function

Could being a bit dense, how do i incorporate this into having this automate process for every document in subfolders?

QA_Compliance_Advisor · #3 09-16-2014, 03:06 AM

Quote:

Originally Posted by gmayor

OK, it is confirmed a form field and the document is unprotected, so the following will handle that. You can re-activate the commented out list section to change the vessel names.

This also revealed a minor bug in my batch process when run in Word 2010 that has now been fixed in version 2.3.

Code:

Function Ddown(oDoc As Document) As Boolean
Dim oFF As FormField
    On Error GoTo Err_Handler
    For Each oFF In oDoc.FormFields
        If oFF.name = "Dropdown1" Then
            With oFF
                .name = "drpVessel"
                'With .Dropdown.ListEntries
                '    .Clear
                '    .Add "Item 1"
                '    .Add "Item 2"
                '    .Add "Item 3"
                '    .Add "Item 4"
                'End With
            End With
            Exit For
        End If
    Next oFF
    Ddown = True
lbl_Exit:
    Exit Function
Err_Handler:
    Ddown = False
    Resume lbl_Exit
End Function

I have found that there is a more problamatic issue, not all dropdown that I want to change are named dropdown1, but there is a logic all the drop down in the list entry has "select Vessel". what logic would I need to have;

If dropdown list entry = "Select Vessel" then

carry out the change (obviously it probably aint that simple)

gmayor · #4 09-16-2014, 04:40 AM

It probably is

The Select Vessel prompt will be the first item in the list so we can read that instead of the field name. The only time you might have a problem is if there are two similar dropdowns in the same document. Only one of them can be called 'drpVessel'. The macro therefore only processes the first such field. For multiple such fields in the same document, you would have to introduce a counter to add to the name.

Code:

Function Ddown(oDoc As Document) As Boolean
Dim oFF As FormField
Dim i As Long
    On Error GoTo Err_Handler
    For Each oFF In oDoc.FormFields
        If oFF.Type = wdFieldFormDropDown Then
            If InStr(1, LCase(oFF.Dropdown.ListEntries(1).Name), "select vessel") > 0 Then
                With oFF
                    .Name = "drpVessel"
                    'With .Dropdown.ListEntries
                    '    .Clear
                    '    .Add "Item 1"
                    '    .Add "Item 2"
                    '    .Add "Item 3"
                    '    .Add "Item 4"
                    'End With
                End With
                Exit For
            End If
        End If
    Next oFF
    Ddown = True
lbl_Exit:
    Exit Function
Err_Handler:
    Ddown = False
    Resume lbl_Exit
End Function

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple entries in dropdown lists	paul_pearson	Word VBA	151	10-18-2023 04:23 PM
Duplicating entries on multiple tabs automatically	jbexley	Excel	0	08-28-2014 04:48 PM
Captions automatically updating all previous entries	jhats	Word	1	07-29-2014 11:53 PM
Dropdown list, Macro	shield5	Excel Programming	7	10-27-2013 01:51 AM
Change cell color everytime a value is selected in dropdown list	angelica_gloria	Excel	4	01-27-2012 06:47 PM