Clearing/reset cells with a button

[Ludo]

After put in lots af data, it would be easy to erase the sheet it in one go, in stead of clearing 'cell by cell".a kind of 'reset' button... and i'm talking about just the data cells, obvious not the formula cells.

I'm quite a newb on programming buttons, is there any one who can help me out with some simple lines.

tnx,
Ludo

 

[erik.van.geit]

Hello, Ludo !!
Welcome to the Board !!!!!!!!!!!!!!

I know who you are

control-click your cells you want to reset
click in the namebox
type a name like "erase_range"

code

Sub test()
Range("erase_range").ClearContents
End Sub

glad to be there whan you posted for the first time !!!

ENJOY the Board !

kind regards,
Erik

 

[NathanPage]

The simplest way I know how to do this is to record the macro you need.

Create the cell, with data in each of the cells that you will want cleared.
Click on Tools -> Macro -> Record New Macro.
Type in a name for the macro, and a shortcut key if you wish
Select each cell in turn that you will want to clear, and press the delete key.

When you've cleared all the cells, stop the macro recording (either Click Tools -> Macro -> Stop Recording, or click the "stop" button that should have appeared on your screen when you started recording the macro)

Now, whenever you hit the shortcut key combination, it will clear those cells. You can also link the macro to a macro button directly.

 

[Ludo]

Thnx guys, it seems to work perfect.

Second problem now is, when i protect the sheet after i made the button, and i wanna test it again, i get an error saying the cells are not changeble ??? Allthou they are unlocked ?

What did i missed ?

 

[erik.van.geit]

Hello, Ludo,

be sure you missed to unlock at least one cell !

try this on a copy of your spreadsheet

Sub test()
For Each cell In Range("erase_range")
If cell.Locked Then cell.Interior.ColorIndex = 6
Next cell
End Sub

locked cells will turn yellow

best regards,
Erik

 

[ptreanor]

An easier way might be to clear just the constants, (ie any cell without a formula). If you need some values to remain just turn them into a formula, eg ="Heading"

The following should clear all non-formula cells:

Sub test()
Cells.Select
Selection.SpecialCells(xlCellTypeConstants).ClearContents
End Sub

 

[Ludo]

Yeps, it runs perfect now, there where a couple cells who where still locked.

Ok since i'm a lazy git, i'll get on with questions: can you validate a button too; like "are you sure you wanna take this action?" when you press the button?

 

[erik.van.geit]

Ok since i'm a lazy git, i'll get on with questions

this will not be invite much people to help: mainly we try to help people which try out things themselves but get stuck on some details
for this time, I'll give you a little present

Sub test()
If MsgBox("sure?",292,"title") = vbNo Then Exit Sub
Range("erase_range").ClearContents 
End Sub

if any problems feel free to reply (don't call me)