Removing commandbuttons on "save as"

[ole_]

Hi,

I have a XLS file called pricelist with 3 sheets and all with 14
commandbuttons, when the pricelist.xls
is "saved as" i dont want the commandbuttons to be saved only the data, but
they of cource still should
remain in pricelist.xls (when im finshed making it im going to make it to a
XLT file)

AHA.

Ole

 

[Bob Phillips]

Hi Ole,

How can they not be saved, yet remain?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ole_]

I have my Pricelist.xls with the commandbuttons and when that file is "save
as"
for exampel "march.xls" i dont want March.xls to have any commandbuttons
but they should still remain in pricelist.xls.

Ole

 

[Bob Phillips]

Okay, it was said actually, just6 didn't see it

In the code just before the SaveAs, delete them. This code deletes all
shapes on a worksheet, including buttons. Just call this routine. You might
want to chyange ACtivesheet to a named sheet, such as Worksheets("Sheet1")


'----------------------------------------------------------------
Sub RemoveShapes()
'----------------------------------------------------------------
Dim shp As Shape
Dim sTopLeft As String
Dim fOK As Boolean

For Each shp In ActiveSheet.Shapes

fOK = True

testStr = ""
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 testStsTopLeftr = "" Then
'keep it
fOK = False
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ole_]

Bob, your a genius, ones again thanks.

and if it can comfort you i'm allmost done with my "project".

Ole

 

[ole_]

Bob, how do i save it without removing the commandbuttons.

When i put the code in Thisworkbook like this:

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

testStr = ""
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 testStsTopLeftr = "" Then
'keep it
fOK = False
End If
End If
End If

If fOK Then
shp.Delete
End If

Next shp

End Sub


The new workbook is without commandbuttons, but also pricelist.xls is
without commandbutton's?

How can i save the code in pricelist.xls without removing the
commandbuttons, don't ask me how i did
it in the test file, i still have the test file and it's with CB.

Ole

 

[Dave Peterson]

I think you got hit by Edit|replace:

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

sTopLeft and testStr were intermingled.

 

[Tom Ogilvy]

If you going to make it an XLT, then the xlt will not be affected by any
code. It will only affect the workbook created from the xlt.

While working with the xlt, you should disable events before saving, or put
a check in your code so the shapes are not deleted if the userid is you (for
example). Or you can comment out the code until you convert the workbook
to an XLT.