Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 recalculating custom function when scrollbar is moved? | Excel Programming | |||
Excel 2007 SP1 - How to correct scrollbar extent? | Excel Discussion (Misc queries) | |||
Colour Cells onSheet | Excel Programming | |||
ScrollBar Sheet problem | Excel Programming | |||
Excel VBA - Userform Texbox/Scrollbar problem | Excel Programming |