Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with creating CLEAR button
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 : ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with creating CLEAR button
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with creating CLEAR button
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with creating CLEAR button
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 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with creating CLEAR button
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. ToolsMacroRecord New Macro. Store macro in: This workbook. You can give it a name and a shortcut combo if desired. EditGo ToSpecialConstants. 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 EditClearContents. Formulas will remain ready for new data input. Gord Dibben XL2002 On Fri, 21 Nov 2003 20:34:44 -0600, SuperJudge wrote: I have recently taken a 2 day course on basic Exel but it did not cover anything about macros. I use a spreadsheet to keep track of paying bills each month and I have seen (at my workplace) that you can insert a button on the speadsheet that once clicked on will delete any 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 :D ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clear button | Excel Discussion (Misc queries) | |||
clear button | Excel Discussion (Misc queries) | |||
Clear Button | Excel Discussion (Misc queries) | |||
create a clear button | Excel Discussion (Misc queries) | |||
Button to clear all entries? | Excel Worksheet Functions |