worksheet_save macro help

[yshridhar]

Hi everybody
My worksheet contains worksheet_change event based on range name "ID". What
i want is when i save or close the workbook i want ID = "". I have written
the following code

Private Sub Worksheet_save()
Range("id") = ""
ActiveWorkbook.Save
End Sub

It works only when i run it from VB window.
Any suggestions.
Thanks in advance
With regards
Sreedhar

 

[papou]

Hello Sreedhar
This can't work since there is no Worksheet_Save event in Excel.
But you can use either Workbook_BeforeSave or Workbook_BeforeClose events
instead.

HTH
Cordially
Pascal

 

[yshridhar]

Hello Pascal
Can you suggest me the BeforeSave or BeforeClose events.

Private Sub Worksheet_BeforeSave()
Range("id") = ""
ActiveWorkbook.Save
End Sub
The modification is not working.
With warm Regards
Sreedhar

 

[papou]

These are **workbook** events as i mentioned.
So you will need to amend and also specify which worksheet, eg:

Private Sub Workbook_BeforeSave()
Worksheets("Your Sheet Name Here").Range("id") = ""
Me.Save
End Sub

HTH
Cordially
Pascal

 

[papou]

I n addition:
Place the code into ThisWorkbook

Cordially
Pascal

 

[yshridhar]

Thank you Pascal
with warm regards
Sreedhar

These are **workbook** events as i mentioned.
So you will need to amend and also specify which worksheet, eg:

Private Sub Workbook_BeforeSave()
Worksheets("Your Sheet Name Here").Range("id") = ""
Me.Save
End Sub

HTH
Cordially
Pascal