excel 2007 problem deleting an onsheet scrollbar

  • Thread starter Thread starter Brian Murphy
  • Start date Start date
B

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