Drop Down Object Selection in Macro

  • Thread starter Thread starter Herman Merman
  • Start date Start date
H

Herman Merman

Two confusing problems:

I have a Macro which Copies a worksheet which contains two dropdow
lists: "Dropdown 8" & "Dropdown 20"

The Macro copies the entire worksheet to a new one within the sam
workbook and appends a (2) to the end. The dropdown lists are no
called "Dropdown 8" and "Dropdown 13" The macro then selects Dropdow
13 and deletes it

ActiveSheet.Shapes("Drop Down 13").Select
Selection.Delete

My problem is that when the Macro is used by someone else on
different PC the dropdown list suddenly becomes "Dropdown 14" and th
Macro Errors when it tries to Select "Dropdown 13". Why doesn'
Dropdown 8 Change?

The same problem applies to several charts on the same worksheet, th
Macro selects each chart and changes the source data for them.

ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.ChartArea.Select

Chart 9 suddenly becomes Chart 10 and again the Macro Errors.

How can I stop excel renumbering the objects within a worksheet?
have tried naming them but it doesn't seem to work as th
Insert>Name>Define function still refers to "Drop Down 14"

Any ideas?

Thanks

Herma
 
I don't believe you can change Excel's behavior. So code around it:


Dim sh as Worksheet
Dim drpDown as DropDown

set sh = activesheet
sh.copy after:=worksheets(worksheets.count)
set sh1 = activesheet
for each drpdown in sh1.dropdowns
if drpdown.topLeftCell.Address = _
sh.Dropdowns("Drop Down 20").topleftcell.Address then
drpdown.Delete
exit for
end if
Next
 
Thanks Tom, this works a treat. Any idea how I can modify it so that it
does the same thing for the charts in the worksheet?

There are 7 Charts and each one needs to be selected in turn to have
their source data changed.

Thanks

Herman
 
assume sh holds a reference to the original sheet copied and the new sheet
is the activesheet
dim obj as ChartObject
dim shObj as ChartObject
for each obj in activesheet.ChartObjects
for each shObj in sh.ChartObject
if obj.TopLeftCell.Address = shObj.TopLeftCell.Address then
obj.Name = shObj.Name
Exit For
end if
Next
Next


this should rename the charts to the same name as on the original sheet.
The only problem might be if this would result in duplicate names (possibly
while the names are being changed). I don't know if this will be a problem
or not.

anyway, after they are renamed, you can use the code you have.
 
Back
Top