excel 2007 problem deleting an onsheet scrollbar

[Brian Murphy]

I wrote the following routine to delete an activeX scrollbar on a
worksheet. When first creating the scrollbar I give the .Name
property a value of my own choosing. My routine tries to use this
property to delete it.

Function DeleteScrollbar(s$) As Boolean
On Error Resume Next
ActiveSheet.OLEObjects(s).Delete
End Function

The above routine worked fine when I first tested it, but later it
would no longer work because of a run time error trying to access the
scrollbar object. Changing OleObjects to Scrollbars or to
DrawingObjects did not help.

I eventually got it to work as follows:

Function DeleteScrollbar(s$) As Boolean
Dim i
On Error Resume Next
With ActiveSheet.OLEObjects
For i = 1 To .Count
If .Item(i).Name = s Then
.Item(i).Delete
Exit For
End If
Next
End With
End Function

What I'm finding is that Excel 2007 acts unpredictably when using
a .Name property to work with on-sheet objects. For example, a For
Each loop would not work in the second routine above. Are there any
special tricks to using .Name properties in Excel 2007?

Thanks,

Brian

 

[Jon Peltier]

Changing OleObjects to Scrollbars or to

DrawingObjects did not help.

Does Shapes(s) work?

- Jon

 

[Brian Murphy]

Yes, it does work! In an instance when OleObjects definitely does not
work, Shapes does work.

Thanks for the tip. I like it a lot better than what I had cobbled
together.

Cheers,

Brian