Newbie Post - Clear All Button

[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

 

[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

 

[Tom Ogilvy]

or even

Sub Macro1()

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

End Sub

skipping the select

--
Regards,
Tom Ogilvy

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

 

[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

 

[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


"