I have a series of drop down boxes created with the Forms menu. The
cell link range for each dropdown is different, as follows:

Cell Link Box1 = DemandBase_A_UndistExp1
Cell Link Box2 = DemandBase_A_UndistExp2
Cell Link Box3 = DemandBase_A_UndistExp3
Cell Link Box...n = DemandBase_A_UndistExp...n

I want to change them all at once to:

DemandBase_A_OtherCost1
DemandBase_A_OtherCost2
DemandBase_A_OtherCost...

I know I can change the entire name using the following code:

Sub Change_Drop_Link()

For Each bx In ActiveSheet.DropDowns
If bx.LinkedCell = "DemandBase_A_UndistExp1" Then
bx.LinkedCell = "DemandBase_A_OtherCost1"
End If
Next

End Sub

But this only replaces one name for another and would require to write
a new name for each change.

Is there anyway to just change the text part of the range name
"DemandBase_A_UndistExp" for "DemandBase_A_OtherCost" and thus do them
all at one go.

Also, the code I use above changes the names in the entire Active
Sheet. I just want to change them for the drop down boxes in a given
part of the sheet or a given range.

Any help will be greatly appreciated. Thank you.

Sub Change_Drop_Link()
For Each bx In ActiveSheet.DropDowns
bx.LinkedCell = Replace(bx.LinkedCell, "UndistExp", "OtherCost", 1)
Next
End Sub

--
Good Luck,
Peg


"hdf" wrote:

> I have a series of drop down boxes created with the Forms menu. The
> cell link range for each dropdown is different, as follows:
>
> Cell Link Box1 = DemandBase_A_UndistExp1
> Cell Link Box2 = DemandBase_A_UndistExp2
> Cell Link Box3 = DemandBase_A_UndistExp3
> Cell Link Box...n = DemandBase_A_UndistExp...n
>
> I want to change them all at once to:
>
> DemandBase_A_OtherCost1
> DemandBase_A_OtherCost2
> DemandBase_A_OtherCost...
>
> I know I can change the entire name using the following code:
>
> Sub Change_Drop_Link()
>
> For Each bx In ActiveSheet.DropDowns
> If bx.LinkedCell = "DemandBase_A_UndistExp1" Then
> bx.LinkedCell = "DemandBase_A_OtherCost1"
> End If
> Next
>
> End Sub
>
> But this only replaces one name for another and would require to write
> a new name for each change.
>
> Is there anyway to just change the text part of the range name
> "DemandBase_A_UndistExp" for "DemandBase_A_OtherCost" and thus do them
> all at one go.
>
> Also, the code I use above changes the names in the entire Active
> Sheet. I just want to change them for the drop down boxes in a given
> part of the sheet or a given range.
>
> Any help will be greatly appreciated. Thank you.
>
>

Peg,

Thank you very much, it works like a charm.

Is there any way to limit the changes to Drop Down boxes within a
certain range, rather than all boxes in an active sheet?