Newbie Post - Clear All Button
 

Ok all I want to do is have a button that will clear all the cells in
workbook with multiple sheets. However there are only certain cell
that i want cleared. The workbook is for a stock/inventory system
have at work and I would only like to clear the cells with inpu
numbers in them. Should I protect all cells that are just words an
formulas (total quantity of stock items) and then leave the input cell
(where I enter the amount of individual stock items) unprotected an
then have a button that just clears the unprotected cells?

If there is code for a button that will do this I would very much b
thankful for any help thank you

--
Message posted from http://www.ExcelForum.com

Newbie Post - Clear All Button
 

one way

place a button from the forms menu on a sheet and attach this code to it

Sub Macro1()

Range("H4,J4:J20,C17,E25,H16,F12").Select
Selection.ClearContents

End Sub

the actual range you use would be the cells you want to delete. The simplest
way to do it is actually select the cells while recording a macro


"Buritoking " wrote in message
...
Ok all I want to do is have a button that will clear all the cells in a
workbook with multiple sheets. However there are only certain cells
that i want cleared. The workbook is for a stock/inventory system I
have at work and I would only like to clear the cells with input
numbers in them. Should I protect all cells that are just words and
formulas (total quantity of stock items) and then leave the input cells
(where I enter the amount of individual stock items) unprotected and
then have a button that just clears the unprotected cells?

If there is code for a button that will do this I would very much be
thankful for any help thank you.


---
Message posted from http://www.ExcelForum.com/

Newbie Post - Clear All Button
 

or even

Sub Macro1()

Range("H4,J4:J20,C17,E25,H16,F12").ClearContents

End Sub

skipping the select

--
Regards,
Tom Ogilvy


"Mike Hughes" wrote in message
...
one way

place a button from the forms menu on a sheet and attach this code to it

Sub Macro1()

Range("H4,J4:J20,C17,E25,H16,F12").Select
Selection.ClearContents

End Sub

the actual range you use would be the cells you want to delete. The
simplest
way to do it is actually select the cells while recording a macro


"Buritoking " wrote in message
...
Ok all I want to do is have a button that will clear all the cells in a
workbook with multiple sheets. However there are only certain cells
that i want cleared. The workbook is for a stock/inventory system I
have at work and I would only like to clear the cells with input
numbers in them. Should I protect all cells that are just words and
formulas (total quantity of stock items) and then leave the input cells
(where I enter the amount of individual stock items) unprotected and
then have a button that just clears the unprotected cells?

If there is code for a button that will do this I would very much be
thankful for any help thank you.


---
Message posted from http://www.ExcelForum.com/

Newbie Post - Clear All Button
 

You could possibly use something like the following. Be very careful, because
it WILL clear all number constants from all sheets in the active workbook
as you requested.

Sub ClearWBofNumberConstants()
Dim sht As Worksheet
For Each sht In Application.Worksheets
On Error Resume Next '-- in case no cells to clear
sht.Range("B:IV").SpecialCells(xlConstants, xlNumbers).Clear
On Error GoTo 0
Next sht
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Buritoking " wrote in message ...
Ok all I want to do is have a button that will clear all the cells in a
workbook with multiple sheets. However there are only certain cells
that i want cleared. The workbook is for a stock/inventory system I
have at work and I would only like to clear the cells with input
numbers in them. Should I protect all cells that are just words and
formulas (total quantity of stock items) and then leave the input cells
(where I enter the amount of individual stock items) unprotected and
then have a button that just clears the unprotected cells?

If there is code for a button that will do this I would very much be
thankful for any help thank you.


---
Message posted from http://www.ExcelForum.com/

Newbie Post - Clear All Button
 

This might be a bit more practical (safer) since it will test for
a specific workbook.

Sub ClearWBofNumberConstants()
Dim sht As Worksheet
If LCase(ActiveWorkbook.Name) < "coxolors.xls" Then
MsgBox "Illegal attempt to clear wrong workbook"
Exit Sub
End If
For Each sht In Application.Worksheets
On Error Resume Next '-- in case no cells to clear
sht.Range("B:IV").SpecialCells(xlConstants, xlNumbers).Clear
On Error GoTo 0
Next sht
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"