Hi, I'm fairly new to VBA's, but it looks like that is what I potentially need if this is even possible. I am looking to automate clearing content in a cell range if another worksheet is selected. Attached is an example workbook with 3 tabs. I am looking for a way to automatically clear the content in cells D3:17 (of current worksheet/tab) if you move to another tab. So if someone fills out tab "Color1" for the cells mentioned and then clicks the tab "Color2", I want the content entered in D3:17 of Color1 to be cleared. Each tab is essentially an option so if a new tab is selected, an individual in theory wont look back at the old tab (or they can start over).
I saw the below code for clearing content, but it looks like you have to manually run that.
Sub Clear_Cells()
Range("D3:D17").ClearContents
End Sub
I also found this code to "run a macro code when activating a sheet from a workbook," but if this is what I need - I could not successfully implement the two codes to work together.
Private Sub Worksheet_Activate()
Call MyMacro
End Sub
Appreciate any input.
Thanks,
Kyle
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.)
Auto Clearing Content on one worksheet if clicking on another.
Answers
Paste the code below to the code module of your worksheet Sheet6 (Color 1).
Private Sub Worksheet_Activate()
Range("D3:D17").ClearContents
End Sub
The location is important. The same procedure in a standard code module (like your Module1) will not be effective because the worksheet's Activate event is being taken note of only by the worksheet's own code module.
There is a Deactivate event as well which I intentionally avoided the use of. I see no point in deleting entries in a sheet no one looks at. So, when the user selects another sheet all contents in Sheet6 remain in place and, in fact, you may still use that content for statistical or informative purposes as you develop your project further in the future. But when the user goes back and looks at the sheet again it is wiped clean at that moment.
You can use the same procedure, possibly with different target ranges, on other sheets. Each procedure is active only on the sheet in whose code module it resides.