excel 2007 problem deleting an onsheet scrollbar
 

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

excel 2007 problem deleting an onsheet scrollbar
 

Changing OleObjects to Scrollbars or to
DrawingObjects did not help.

Does Shapes(s) work?

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



Brian Murphy wrote:
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

excel 2007 problem deleting an onsheet scrollbar
 

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


On Oct 14, 9:45 am, Jon Peltier wrote:
Changing OleObjects to Scrollbars or to
DrawingObjects did not help.

Does Shapes(s) work?

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.http://peltiertech.com/