How do I insert a 'clear all' button in excel?

[Guest]

I would like to insert a button into a quotation form that will clear data
from all unlocked cells ie name, address equipment and costings. The form
was created in Excel.

 

[Paul B]

Nick, this will unlock the sheet, clear all the unlocked cells on a
worksheet, protect the sheet, is this what you want? you say form, but it
sounds like you are talking about unlocked cells in the worksheet

Sub Clear_Unlocked()
Dim Cel As Range
Const Password = "123" '**Change password here**
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Password
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Locked = False Then Cel.Formula = ""
Next
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Nick, this will unlock the sheet, clear all the unlocked cells on a
worksheet, protect the sheet, is this what you want? you say form, but it
sounds like you are talking about unlocked cells in the worksheet

Sub Clear_Unlocked()
Dim Cel As Range
Const Password = "123" '**Change password here**
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=Password
For Each Cel In ActiveSheet.UsedRange.Cells
If Cel.Locked = False Then Cel.Formula = ""
Next
ActiveSheet.Protect Password:=Password
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003




Hi Paul and thanks for your message. The form I have created is for quotations and used by 5 people, all parts of the form are locked off apart from the parts where they can fill in details like name, address and prices etc. What I would like to do is add a button that allows the form to be reset ie removing all data that the last operator has inserted into the form. Thanks for your help.

Nick

 

[Paul B]

Nick,
"I would like to insert a button into a quotation form that will clear data
from all unlocked cells "

The code I posted will do that, if this is not what you want what cells do
you want to clear? A2:A10, C5, H10......, you may also want to try and save
the file as a template, then when you open it you will have a blank copy
each time.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

quotations and used by 5 people, all parts of the form are locked off apart
from the parts where they can fill in details like name, address and prices
etc. What I would like to do is add a button that allows the form to be
reset ie removing all data that the last operator has inserted into the
form. Thanks for your help.