hi,
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
Paul
hi,
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
Paul
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
Sh.Range(ClearRange).ClearContents
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.
Private Sub Workbook_Open()
Application.OnTime TimeValue("08:01:00"), "Setup"
End Sub
Option Explicit
Private xlApp As XlEvents
Private Sub Workbook_Open()
Set xlApp = New XlEvents
Application.OnTime TimeValue("08:01:00"), "Setup"
End Sub