Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
Hi Ole,
How can they not be saved, yet remain? -- HTH RP (remove nothere from the email address if mailing direct) "ole_" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
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" skrev i en meddelelse ... Hi Ole, How can they not be saved, yet remain? -- HTH RP (remove nothere from the email address if mailing direct) "ole_" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
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_" wrote in message ... 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" skrev i en meddelelse ... Hi Ole, How can they not be saved, yet remain? -- HTH RP (remove nothere from the email address if mailing direct) "ole_" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
Bob, your a genius, ones again thanks.
and if it can comfort you i'm allmost done with my "project". Ole "Bob Phillips" skrev i en meddelelse ... 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_" wrote in message ... 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" skrev i en meddelelse ... Hi Ole, How can they not be saved, yet remain? -- HTH RP (remove nothere from the email address if mailing direct) "ole_" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
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. Bob Phillips wrote: 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_" wrote in message ... 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" skrev i en meddelelse ... Hi Ole, How can they not be saved, yet remain? -- HTH RP (remove nothere from the email address if mailing direct) "ole_" wrote in message ... 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing commandbuttons on "save as"
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. -- Regards, Tom Ogilvy "ole_" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
"Save" and "Save As" options greyed out - "Save as Webpage" option | Excel Discussion (Misc queries) | |||
Removing "Save As" Command from an excel file. | Excel Worksheet Functions | |||
save and restore "Workbook Menu Bar" & "Cell" menus | Excel Programming |