Help with creating CLEAR button

  • Thread starter Thread starter SuperJudge
  • Start date Start date
S

SuperJudge

I have recently taken a 2 day course on basic Exel but it did not cove
anything about macros. I use a spreadsheet to keep track of payin
bills each month and I have seen (at my workplace) that you can inser
a button on the speadsheet that once clicked on will delete an
information typed and leave the spreadsheet with the formulas in tact.
Any ideas on how I could do this would greatly be appreciated.

Thanks,
SuperJudge :
 
The easiest way to do this would be to just record a macro. You can do this by choosing Tools:Macro:Record New Macro. Name your macro, click OK, and then manually perform the activities you want to accomplish. Once complete, go back to Tools:Macro:Stop Recording. Now you have your macro.

You can either run the macro by going through Tools menu again, or if you want a button, goto View:Toolbars:Forms. This will show the Forms toolbar which you can choose Command Button off from. Create your Command Button, right click on it and choose Assign Macro. Once you assign the macro you recorded earlier, clicking the button will initiate it.
 
First you'll need to create a command button from the controls bar. The
after creating the button, right click and select "view code". Nex
you'll need to input the following format depending on your sheet
setup..... Clear out a range of cells:
Range("A1:B3") = 0
To clear out specific cells then do: Range("A1") = 0 next line
Range("B1") = 0 and so on until you get all the cells you want.
Then once the button is clicked it will change the values to zero
 
Another option you can try

Sub clear()
Dim Msg, Style, Title, Response, MyString, varfilestring
Msg = "Continuing will Delete all Data (i.e. Start New Week)" & Chr(13
_
& "Default values will replace" ' Define message.
Style = vbYesNo + vbDefaultButton2 ' Define buttons.
Title = "Erase Form Data" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Range("B7:G57").Select
Selection.ClearContents
Range("I7:N57").Select
Selection.ClearContents
Range("P7:U57").Select
Selection.ClearContents
DONE
Else ' User chose No.
MyString = "No" ' Perform some action.
DONE
End If
varfilestring = "Blank_Manning_v3" & ".xls"
ActiveWorkbook.SaveAs varfilestring, FileFormat:=xlNormal
password:="", WriteResPassword:="", ReadOnlyRecommended:=False
CreateBackup:=True
End Sub

Will need modification for what you actually want. This way you a
least get the chance not to run the macro should you trigger i
accidental
 
Super

To delete all but formulas you don't need a macro, but you can record yourself
doing the following steps and produce a macro that you can assign to a button.

Tools>Macro>Record New Macro. Store macro in: This workbook. You can give it
a name and a shortcut combo if desired.

Edit>Go To>Special>Constants. You can un-check what you want to find on the
sheet(text, numbers, etc.) or just leave all checked.

OK your way out then Edit>Clear>Contents.

Formulas will remain ready for new data input.

Gord Dibben XL2002
 
Back
Top