remove vba and functions when saving worksheet
 

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

remove vba and functions when saving worksheet
 

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)

"john tempest" wrote in message
...
can i remove vba and functions when saving a worksheet
thankyou john tempest

remove vba and functions when saving worksheet
 

Take a look at this link...

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

Jim Thomlinson


"john tempest" wrote:

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

remove vba and functions when saving worksheet
 

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" wrote:

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)

"john tempest" wrote in message
...
can i remove vba and functions when saving a worksheet
thankyou john tempest

remove vba and functions when saving worksheet
 

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)

"john tempest" wrote in message
...
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" wrote:

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)

"john tempest" wrote in message
...
can i remove vba and functions when saving a worksheet
thankyou john tempest

remove vba and functions when saving worksheet
 

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

"john tempest" wrote:

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" wrote:

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)

"john tempest" wrote in message
...
can i remove vba and functions when saving a worksheet
thankyou john tempest