VBA to Clear contents from a range if text appears in a certain cell



I am looking to clear the contents of E120:K152 if the word STOP appears in cell G1
I need this to apply to 60 seperate sheets

There are 3 other sheets in teh workbook that it should not apply to

Thanks for any help



Selected Answer

From a worksheet, press Alt+F11 to open the VB Editor. In the VBE's Project Explorer window right-click on your workbook's VBA Project. Select 'Insert' and 'Class module'.

In the VBE's Properties window (make it available from the View menu), change the new class module's name, initially "Class1", to "XlEvents" (no quotation marks). Paste the code below into the code area on the right of your screen.

Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
    Set App = Application
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, _
                            ByVal Target As Range)
    ' 18 Jan 2018
    ' Enter the names of the tabs which are to be excluded here.
    ' Separate names with a slash.
    Const Exclude As String = "SubTotal/Update/Word"
    Const StopCell As String = "G1"                 ' change as required
    Const ClearRange As String = "E120:K152"        ' change as required
    If InStr(1, Exclude, Sh.Name, vbTextCompare) = 0 Then
        With Target
            If .Address = Range(StopCell).Address Then
                If StrComp(Trim(.Value), "stop", vbTextCompare) = 0 Then
                    App.EnableEvents = False
                    App.EnableEvents = True
                End If
            End If
        End With
    End If
End Sub

Modify the value of "Const Exclude" to hold the names of the tabs you wish to remain unaffected by the macro's actions.

Back in the Project Explorer window, double-click on 'ThisWorkbook' to open the code sheet of the workbook's code module. Paste the code below into the code area on the right (presumably empty).

Option Explicit

Private xlApp As XlEvents

Private Sub Workbook_Open()
    Set xlApp = New XlEvents
End Sub

Save the workbook in XLSM (macro-enabled) workbook.

Place the cursor anywhere in the line 'Set xlApp = New XlEvents' and press F5 to run that procedure. Nothing will happen, but you are now all set to enter "stop" (case-insensitive) in any sheet (exclusions excluded) and have the range defined by 'Const ClearRange' wiped on that sheet.

You will not have to run the Private Sub Workbook_Open every time. It will run automatically whenever you open the workbook. Its job is to set the variable xlApp which responds to the change you make in G1 of any sheet. If your workbook crashes xlApp will be lost and thereby also its functionality. You will have to close and re-open the workbook or run the on_open procedure in the 'ThisWorkbook' module manually with F5.

The code will not check the existing value of G1. It will resond to a change made in that cell and take action if the value = "Stop" after the change.



Hi,  thank you for your response, I did however come across a problem ..
When I get tp the part "Paste the code below into the code area on the right (presumably empty)"  (refering to the second code above) there is in fact the following code already in that window :-

Private Sub Workbook_Open()
    Application.OnTime TimeValue("08:01:00"), "Setup"
End Sub

I have tried to paste the new (your) code above and below the exsisting code but get the same result when I place the cursor on the line and press F5,  it says Compile error, ambiguous name detected: Workbook_Open

I am a begginer at this and would appreciate your further input
Thanks and regards

Sherman51 (rep: 6) Jan 18, '18 at 10:07 am
That is correct. You can only have one Open event procedure. Including your existing code the 'ThisWorkbook' code sheet should have th content below.
Option Explicit

Private xlApp As XlEvents

Private Sub Workbook_Open()
    Set xlApp = New XlEvents
    Application.OnTime TimeValue("08:01:00"), "Setup"
End Sub
Variatus (rep: 2938) Jan 18, '18 at 6:53 pm
Thank you for your further help, however,  when I remove the previous code and add the code that you have provided in the "ThisWorkbook" .. I get an error ..

In the line " Private   x1app As X1Events"   the text apart from "Private"   gets highlited Blue and an error box comes up with "Compile Error:  User defined type not defined"

thanks again ... 
Sherman51 (rep: 6) Jan 19, '18 at 1:26 pm
You typed, didn't copy/paste, lol:. So, xl became x1 (lower case L replaced with 1) both in xlApp and XlEvents. Of course, neither of these is defined.
Variatus (rep: 2938) Jan 19, '18 at 10:28 pm
thats a funny thing with the 1's and lower case l's .. in the code they look exactly the same .. I did in fact copy and paste the code ...  if you look at other code, like the word "Enable" for example or "Explicit" , the l looks like 1 ..
Anyway,  I must have been doing something else wrong, i think it was probably that I was adding the code to a sheet that already had macros running (guessing thats a schoolboy error)
So in the end we have Success ... So a big thank you for your help
All the best
Sherman51 (rep: 6) Jan 20, '18 at 6:30 am
Hi Paul, I'm glad you got it working. Please mark the answer as "accepted" on this forum. Thank you.
Variatus (rep: 2938) Jan 20, '18 at 6:33 am
sorry,  I actually spoke too soon,  I set it up to run today ...
the cell G1 changes from an outside source from being blank to dispalying "Stop", when the cell G1 changes nothing happens,  if I type "stop" in the cell manually then it works ..
so very nearly there, is there a way around this ?
Sherman51 (rep: 6) Jan 20, '18 at 12:53 pm
Action is taken when G1 changes. That was the theory. Now, in practise, it isn't that at all. You want G1 checked whether it contains "Stop" after another event occurred. That event you vaguely describe as "an outside source", and that vagueness describes accurately how far you are from a solution.
In fact, I think it is so far from what we have been discussing here that it doesn't fit into the current thread. I don't want the required clarification of your question and reformatting of the answer somewhere at the end of a long discussion where no one who is interested in it will ever look.
I think your original question was answered as asked. Close the thread. Award point for the answer. The code is useful to many just as it is. Your new question should be how to make the existing code do exactly what it does but not (or not only?) when G1 is changed but when another event occurs which you will yet have to describe. Please don't refer to this thread when you ask your question. Instead, post the code and phrase the question as completely independent of all precident so that all formum visitors can follow. Thank you.
Variatus (rep: 2938) Jan 20, '18 at 8:27 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login