VBA Dropdown change list Entries automatically

QA_Compliance_Advisor · #1 09-11-2014, 03:15 AM

I would like to know how i would go about changing a dropdown box in mulitple documents and change the name of the dropdown name.

currently the dropdown is named 'dropdown1' it needs to be changed to 'drpVessel'.

does any one have any suggestion the best way around this?

macropod · #2 09-11-2014, 07:20 PM

What kind of dropdown and, aside from the name change, what other changes?

QA_Compliance_Advisor · #3 09-11-2014, 11:42 PM

Quote:

Originally Posted by macropod

What kind of dropdown and, aside from the name change, what other changes?

Content Control dropdown list, the name of the dropdown and the content of the dropdown list.

macropod · #4 09-11-2014, 11:51 PM

So which do you want to change - the tag or the title?
As for the content, what do you want to do - add items, delete items, change their display names or values, something else?
If the changes are substantial it may be simpler to delete the existing controls & replace them with entirely new ones.

QA_Compliance_Advisor · #5 09-12-2014, 12:30 AM

Quote:

Originally Posted by macropod

So which do you want to change - the tag or the title?
As for the content, what do you want to do - add items, delete items, change their display names or values, something else?
If the changes are substantial it may be simpler to delete the existing controls & replace them with entirely new ones.

some of them are from 2003-2007 which have Bookmark so would like to change the bookmark name, if I am correct in thinking that is how content control dropdown work in 2003-2007.

gmayor · #6 09-12-2014, 12:22 AM

One possibility is to use a variation of the following function in conjunction with http://www.gmayor.com/document_batch_processes.htm which will handle the files and folders.
Make whatever changes to 'oCC' that you require and the add-in will do the same for all matching files in the folder.

Code:

Function Dropdown(oDoc As Document) As Boolean
Dim oCC As ContentControl
    On Error GoTo Err_Handler
    For Each oCC In oDoc.ContentControls
        If oCC.Title = "Dropdown1" Then
            With oCC
                .Title = "drpVessel"
                .Tag = .Title
                With .DropdownListEntries
                    .Clear
                    .Add "Item 1"
                    .Add "Item 2"
                    .Add "Item 3"
                    .Add "Item 4"
                End With
            End With
            Exit For
        End If
    Next oCC
    Dropdown = True
lbl_Exit:
    Exit Function
Err_Handler:
    Dropdown = False
    Resume lbl_Exit
End Function

gmayor · #7 09-12-2014, 12:43 AM

Content controls were launched in Word 2007, and improved in 2010. They are not compatible with 2003. I raised an eyebrow when you mentioned the name Dropdown1 which suggested legacy form fields as by default content control dropdowns do not have titles, and if you were adding one you would surely type something more meaningful.

The code I posted will not of course work with legacy form fields. For that to work we would need more information about whether the form is protected and whether it is protected with a password.

Now also you are talking about 'some' of the documents. A batch process can accommodate different document types, but it will perform the same task on each.

QA_Compliance_Advisor · #8 09-12-2014, 12:51 AM

Quote:

Originally Posted by gmayor

Content controls were launched in Word 2007, and improved in 2010. They are not compatible with 2003. I raised an eyebrow when you mentioned the name Dropdown1 which suggested legacy form fields as by default content control dropdowns do not have titles, and if you were adding one you would surely type something more meaningful.

The code I posted will not of course work with legacy form fields. For that to work we would need more information about whether the form is protected and whether it is protected with a password.

Now also you are talking about 'some' of the documents. A batch process can accommodate different document types, but it will perform the same task on each.

The documents are not Password protected. Unfortunately those responsible for inserting the dropdowns are not aware of how to porgam and didn't understand the significance of the naming of things.

I have docs made in 2003, 2007 and 2010. What info do you need.

furthermore have have programmed to convert all 2003-2007 docs to be 2010 can that help?

gmayor · #9 09-12-2014, 01:01 AM

Can you attach a sample document so we can see what we are dealing with?

QA_Compliance_Advisor · #10 09-12-2014, 01:09 AM

Quote:

Originally Posted by gmayor

Can you attach a sample document so we can see what we are dealing with?

here is a sample

gmayor · #11 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 · #12 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 · #13 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 · #14 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

gmayor · #15 09-12-2014, 05:04 AM

You will need the add-in which handles the folders and sub folders - http://www.gmayor.com/document_batch_processes.htm
which I referred to in my first post. Run the above code as a custom process.
The instructions are on the web page.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple entries in dropdown lists	paul_pearson	Word VBA	154	11-08-2025 03:51 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