Next time sheet is touched, remove items from cells?

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I had this before and did not get any takers. I think the chalenge is more
getting to do it once and not to check each time?

=============

Not sure how to do this, but I have a sheet that I have a macro export data
and update a field to say processed in cell F5. I want to know if there is a
way to remove that if someone does anything to the sheet like update a
description or update numbers.

This is more a control thing so I know that the file will match the export
and if someone updates something on the sheet, then the word "processed" in
cell F5 will be gone.

thanks in advance
 
Here is some code that you can put in the sheet. Right click the sheet tab
and select view code. Paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("F5").Value = ""
End Sub

Whenever anything is changed on the sheet it makes Cell F5 = "". Note that
you may need to do some work on your existing macros as they will be making
changes to the sheet and causing this code to fire. In that case you will
need to turn events off while that procedure runs and back on when it is
finished...

Application.enableevents = false
'your import code
Application.enableevents = true
 
Sheet event code to clear F5 if any cell other than F5 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$5" Then
On Error GoTo endit
Application.EnableEvents = False
Me.Range("F5").Value = ""
End If
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Thanks Guys - this worked after figuring out where to turn on and off. My
compliance people will be happy.

have a great night
 
Back
Top