Autoclear Spreadsheet on Opening

[mjg060468]

Hi

Hoping someone can help me.

I have a workbook, which contains 6 spreadsheets.

One of the spreadsheets is a data capture page called 'MUST BE COMPLETED FIRST!!'. This page then populates a spreadsheet entitled 'JUNE 2013'

What I would like to know is what is the best way for this workbook to autoclear certain cells within the data capture page only when the user opens the workbook.

I don't want it to clear any of the cells from the other worksheets. If anyone can help it would be greatly appreciated

Cheers

[Special-K]

"...to autoclear certain cells within the data capture page only..."

That would depend on what the certain cells are, which currently would be a guess from us all...

[Special-K]

"...to autoclear certain cells within the data capture page only..."

That would depend on what the certain cells are, which currently would be a guess from us all...

[Jakobshavn]

Here is a macro-enabled solution. In this example, cells A1 thru A10 are cleared. Include the following event macro in the workbook code area:

Please Login or Register  to view this content.

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

[mjg060468]

Oops sorry

Cells are B2:B4,B7,B8,B17,F6:F28,M2,M6,M20,I7:I9,I15:I17,N14

[mjg060468]

Here is a macro-enabled solution. In this example, cells A1 thru A10 are cleared. Include the following event macro in the workbook code area:

Please Login or Register  to view this content.

Because it is workbook code, it is very easy to install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (workbook code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Thanks for that. I have tried it and it doesn't seem to work. Also I am working with Excel 2010 and when I right click the mouse on the small excel icon it doesn't give me the option of opening vba.

So went into VBA on the developer menu and added the code.

Could it be that I have a pop up which comes up when they open the workbook as a whole?

Regards

[Jakobshavn]

Perhaps its in the wrong place. If you can get the VBA window to open, you should see two panes. In the left-hand pane, right the tiny ThisWorkbook icon and select View Code.

Paste the code in the resulting right hand pane.

[mjg060468]

Perhaps its in the wrong place. If you can get the VBA window to open, you should see two panes. In the left-hand pane, right the tiny ThisWorkbook icon and select View Code.

Paste the code in the resulting right hand pane.

Brilliant, that has worked, however it has also cleared the conditional formatting I had in some of the cells. Is there a way round that?

[Jakobshavn]

Try replacing:
.Clear
with:
.ClearContents

[Jakobshavn]

Try replacing:
.Clear
with:
.ClearContents

[mjg060468]

Awesome, works perfectly thank you so much

[Jakobshavn]

You are very welcome!