Changing Names of Multiple Cell Link Ranges - Forms DropDown

  • Thread starter Thread starter hdf
  • Start date Start date
H

hdf

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
 
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?
 
Back
Top