This post will guide you how to clear the contents of a cell or range of cells on open of the workbook in excel. How do I clear cells before workbook close or open with VBA code in excel. How to clear cells on workbook open in excel.
Clear Cell Content on Open or Exit
Assuming that you have workbook and you want to clear the contents of the specified cells or range (B1:B5) whenever you open the workbook file or whenever you close the workbook file. How to achieve it? you can do the following steps to achieve the result.
Note: Before you run the VBA macro code, you need to save as the current workbook, and set the save as type as: excel Macro-Enabled Workbook.
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module
#4 paste the below VBA code into the code window. Then clicking “Save” button.
clear cell on workbook open:
Private Sub Workbook_Open() Application.EnableEvents = False Worksheets("Book1").Range("B1:B5").Clear Application.EnableEvents = True End Sub
clear cell on workbook close:
Private Sub Workbook_BeforeClose(Cancel As Boolean) Worksheets("Book1").Range("B1:B5").Clear End Sub
So far, when you close or open this workbook, and it will clear the contents of the specified cells or range of cells automatically.