Newbie Post - Clear All Button

B

Buritoking

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
 
M

Mike Hughes

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
 
T

Tom Ogilvy

or even

Sub Macro1()

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

End Sub

skipping the select

--
Regards,
Tom Ogilvy


Mike Hughes said:
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
Click to expand...
 
D

David McRitchie

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
 
D

David McRitchie

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

"
 
Top