I have a sheet with multiple dropdown boxes (form controls, not ActiveX). There's a dropdown at the top of the page with a list of people's names, and then each line item on the sheet has a dropdown with the same list of names. I would like for the user to select a name from the top dropdown, click a button, and have ALL the rest of the dropdowns on the page set themselves to the same name. (The user will then make individual selections on the various line items that need to have a different name selected. For the purposes of what we're doing, each line item is a task that needs to be assigned to a certain person. Most of them will be performed by the person chosen in the top dropdown, but some of them will be performed by someone else, hence the need for the dropdowns.)

What I can't figure out is how to set or rest a whole bunch of dropdowns at once. This is what I was playing with:

Sub UserForm_Initialize()

ActiveSheet.[G1:G10000].DropDowns.Clear
ActiveSheet.[G1:G10000].DropDowns.ListIndex = 3
ActiveSheet.[G1:G10000].DropDowns.SetFocus

End Sub
But of course I get "Run-time error '438': Object doesn't support this property or method." Is there a way to set the value of multiple dropdowns at once?