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