remove vba and functions when saving worksheet

[Guest]

can i remove vba and functions when saving a worksheet
thankyou john tempest

 

[Bob Phillips]

In the save itself? If yes, see http://www.cpearson.com/excel/vbe.htm on how
to delete a code module.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Take a look at this link...

http://www.cpearson.com/excel/vbe.htm

 

[Guest]

this is the code i am using at the moment
Private Sub CommandButton1_Click()
Dim wkbk As Workbook, sh As Worksheet
Worksheets("TIMESHEET").Copy
Set wkbk = ActiveWorkbook
' get rid of all cell formulas
For Each sh In wkbk.Worksheets
With sh.UsedRange
.Value = .Value
End With
Next
wkbk.SaveAs "C:\Documents and Settings\Administrator\My
Documents\HOWDENS\TIMESHEET\ " & Sheets("TIMESHEET").Range("B5") & ".xls"
wkbk.Close
End Sub
this code removes the functions but the command button is stil functional
but does not do anything.when i save the worksheet i want the command button
to be non functional or if possible disappear altogether. am i asking too much
thankyou john tempest

 

[Bob Phillips]

Assuming they are control toolbox buttons, add this at the end

ActiveSheet.OLEObjects("CommandButton1").Delete


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

thank you bob that worked a treat. the only thing is when i inserted it at
the end it cleared my active worksheet and the saved worksheet,so i inserted
it as shown which only removes it from the saved worksheet
thanks again john tempest

Private Sub CommandButton1_Click()
Dim wkbk As Workbook, sh As Worksheet
Worksheets("TIMESHEET").Copy
Set wkbk = ActiveWorkbook
' get rid of all cell formulas
For Each sh In wkbk.Worksheets
With sh.UsedRange
.Value = .Value
'getrid of command button'
ActiveSheet.OLEObjects("CommandButton1").Delete
End With
Next
wkbk.SaveAs "C:\Documents and Settings\Administrator\My
Documents\HOWDENS\TIMESHEET\ " & Sheets("TIMESHEET").Range("B5") & ".xls"
wkbk.Close
End Sub