Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Shapes
Hi NG,
I have the following code that removes my commandbuttons on "save", it's a XLT file with 3 sheets, my problem is that it only removes the commandbuttons on the active sheet and i need it to remove the commandbuttons on all 3 sheets in my workbook. I can't seem to find anything were you can do it on all sheets?? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) '---------------------------------------------------------------- 'Sub RemoveShapes() '---------------------------------------------------------------- Dim shp As Shape Dim sTopLeft As String Dim fOK As Boolean For Each shp In ActiveSheet.Shapes fOK = True sTopLeft = "" On Error Resume Next sTopLeft = shp.TopLeftCell.Address 'Autofilter and Data Validation dropdowns 'don't seem to have a topleftcell address. On Error GoTo 0 If shp.Type = msoFormControl Then If shp.FormControlType = xlDropDown Then If sTopLeft = "" Then 'keep it fOK = False End If End If End If If fOK Then shp.Delete End If Next shp End Sub AHA. Ole |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Shapes
If you're just getting rid of commandbuttons from the control toolbox toolbar,
it may be better to just go after them: Option Explicit Sub testme() Dim OLEObj As OLEObject Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets For Each OLEObj In wks.OLEObjects If TypeOf OLEObj.Object Is MSForms.CommandButton Then OLEObj.Delete End If Next OLEObj Next wks End Sub ole_ wrote: Hi NG, I have the following code that removes my commandbuttons on "save", it's a XLT file with 3 sheets, my problem is that it only removes the commandbuttons on the active sheet and i need it to remove the commandbuttons on all 3 sheets in my workbook. I can't seem to find anything were you can do it on all sheets?? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) '---------------------------------------------------------------- 'Sub RemoveShapes() '---------------------------------------------------------------- Dim shp As Shape Dim sTopLeft As String Dim fOK As Boolean For Each shp In ActiveSheet.Shapes fOK = True sTopLeft = "" On Error Resume Next sTopLeft = shp.TopLeftCell.Address 'Autofilter and Data Validation dropdowns 'don't seem to have a topleftcell address. On Error GoTo 0 If shp.Type = msoFormControl Then If shp.FormControlType = xlDropDown Then If sTopLeft = "" Then 'keep it fOK = False End If End If End If If fOK Then shp.Delete End If Next shp End Sub AHA. Ole -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel document with shapes on it but the shapes do not print | Excel Worksheet Functions | |||
Naming Auto Shapes and Creating new Shapes | Excel Discussion (Misc queries) | |||
When drawing shapes in excel the shapes keep disappearing | Excel Discussion (Misc queries) | |||
How can i get more 3D shapes for Auto shapes in excel? | Excel Discussion (Misc queries) | |||
Shapes In ActiveSheet | Excel Programming |