Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA to clear cells based on the change of a cell via a formula

0

hi,
I am looking to clear the contents of E120:F152   and  H120:K152 if the contents of cell G117 is automatically populated by the word "stop" The spreadsheet is connected to software that does this ( I believe this to be a worksheet Change event) I am a beginner with vba and don't know how else to tell it  .

I need this to apply to 60 seperate sheets.

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

Thanks for any help
Paul

Answer
Discuss

Discussion

The easiest way to do this would be to incorporate the action into the existing Worksheet_Change event procedure. Sort of, when you write "Stop" into G1 Then also do something else". You might add the code to your question or put it into a workbook that you can attach.
Variatus (rep: 4889) Jan 24, '18 at 10:56 pm
I'm sorry but T really don't understand,  I don't have any code to add .. there is no code, G1 is populated by the software that feeds it.
I think I will leave it there and say thank you for you help.
Regards
Paul
Sherman51 (rep: 6) Jan 25, '18 at 5:41 am
Just in case you want to get back to this at some time in the future:- Software consists of code. It's the code in some software that writes "Stop" to your cell. That much is guaranteed. The software you are using is the Excel application. Excel doesn't write "Stop" to any cells. Therefore it can be excluded. It must be a program that runs when Excel runs. That limits its location to either in the workbook or embedded in the Exel application.
In the workbook you probably checked all the code sheets. The code you are looking for might be in a class module. You should check all modules. If it is definite that the code isn't in the workbook itself it must be in an addin. The addin needs not be open for your inspection and modification and it needs not be written in VBA. So that might prove to be a dead end.
Variatus (rep: 4889) Jan 25, '18 at 11:33 pm
The alternative I have suggested is to create your own event. Read "create" as "think up". The question to answer is, "At what time in your work flow would you need Excel to check if "Stop" was inserted. This is probably something you know quite well from, or can find out by, observing when "Stop" appears in the cell. The theory is that if you can determine the conditions under which "Stop" might appear you can also write a program that checks if it actually did appear when those same conditions are met.
The broadest application of "I don't know" is to create an alert. A macro might be made to run at times unrelated to your work flow, like when you open the workbook or save it or even when you click a button, and take action if the "Stop" is found. Of course, that is the most unreliable solution because you wouldn't know how long it has been there already.
Variatus (rep: 4889) Jan 25, '18 at 11:33 pm
Add to Discussion



Answer the Question

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