Free Microsoft Excel 2013 Quick Reference

Button to clear all entries?

I am creating a fill in form in excel 2000. I am locking everything except
the cells I need filled in. I need a way to clear all entries in these
cells after a form is completed and printed. This way the next user has a
clean form to begin with. How can I do this...Thanks..Randy


Hi,

Is it at all possible to make a 'clear' button on my excel sheet to clear all existing cell entries including listboxes to all unprotected cells?

Kind regards,

Dave

I need to know how to write the code for a command button to clear all
filters for a spreadsheet that contains several columns of lists. I want to
click the button and it clear all fitered list back to "all"

I want to create a button that, when pressed will clear all rows that have "Y" in column E. How do I accomplish this? I know nothing about macros, so please keep that in mind when explaining. I really appreciate the help!

-darkwood

Hello I was wondering if exists any way to clear all the textboxes, comboboxes and checkboxes inside on a user form

I was several textboxes, comboboxes and checkboxes in a userform all of this objects have a different name
i.e. cbot2, cbtr5, cb24, tbft4, tr34, ch34, chr4r .....
all on this objects are inside on the userform ''scenario"

and i have the button "clearbutton''

Private Sub clearbuttonClick()
cbot2=""
cbtr5=""
etc..
End Sub
I don't know if there is other way to do this.
the issue is, I need to create aprox 50 more objects in different multitabs, thanks in avance.

Hi,

I have a bunch of combo boxes and text boxes that require clearing once the data is saved into a database, I have put in a command button but want to tell it how to clear all of the data ie The command button says "Clear contents"

I have a large Listbox in a Userform that has 5 columns and can have up
to 1200 row entries. I have run into a curious behavior.

I have buttons to Select all entries in the Listbox and to Clear all
selections in the Listbox. My code is virtually idential to do the
Select All and Clear All. Here's the curious part: when I have ~ 1000
rows in the Listbox, it takes ~ 3 seconds to do a Clear All, but only ~
1 second to do the Select All. With a small number of rows, it
impossible to see any difference, but with a large number of entries it
takes noticeably longer to do the clear I have written the Clear All
code as fast as I can think of (no calculations within the loop). I
also tried turning off screen updating and calculations, but it doesn't
seem to make any difference.

Does anyone know of a faster way to do the clear? Here is my code.

Private Sub btnClearAll_Click()
Dim I As Integer
Dim count As Integer

With NameForm.ListBox1
count = .ListCount - 1
For I = 0 To count
.Selected(I) = False
Next I
End With
End Sub

The only difference between the Clear All code and the Select All
codeis False/True in line .Selected(I) = False.
Any ideas? Why would be it faster to do the selection?

Any info would be appreciated,
Thanks,
Jim

Hello

I have a triple macro query, i hope this is allowed, if not i apolagise.

I was hoping somebody could help as i am now stumped!
[LIST=1]
first macro query, my workbook "cpt" takes data from a workbook called "linking" but the data never travels over immediately, i have to save linking and then update the linked data manually through edit, links, update.

I wish to assign this to a button already set up. Ideally i wish to press a button on my linking workbook and this automatically transfer the data over, is there a way to do this?
at the moment, linking will not autosave, and update will not auto update, i have tried using the macro recorder to no avail, can anyone help?
[LIST=2]
on my workbook, i have set up a number of temp tabs, i can clear these, but occasionally a rogue one appears, i guess there is an error in my code somewhere.

Is there any way to clear all tabs except the main one named "totals"? Ideally i would like this in macro format and add it to my existing code?3
My next query is, if my data meets a certain amount of criteria, it will be saved, but i only need it to be saved once. If the data is already saved i get the standard, this file is already saved do u wish to continue?

If the data is already saved is there a code that will make this message not appear, and not try to resave my data?

Any help with these will be greatly appreciated, i have had a good go with the macro recorder and reading other threads but am stumped, can anyone help?

Kind regards

Joe

I'm not sure if this can be done in Excel or not. I'm more Access-grounded and have used command buttons to do all sorts of things in an Access form, but here it goes...

In an Excel spreadsheet, I have certain cells in which the user will input values. They are A1, B4, B5 and C4. After the user inputs these values, other cells (that have formulas) display values. This is used for "What-If" scenarios.

If the user wants to input different numbers for the "What-If" scenarios, I want the user to be able to click a command button, and this would reset the cells (A1, B4, B5 and C4) to their original blank values.

How can I do this with the command buttons? I tried creating a command button, but I can't figure out the proper syntax for the code, assuming that this is the correct method.

Thanks in advance.

i created a field in which you can enter values in different spaces and it
will calculate, these number accordingly.... how do i create a button to
clear the values inputed, so that the "calculator" can be used again

I have a command button to clear sheet, cant get it to clear the checkboxes?

HTML Code: 
 Range("A16:A52,B16:B52,I16:I52,A62:A115,B62,B115,H10,C10:C13,I62:I115,checkbox1,checkbox2").Select
 Range("A51").Activate
    Selection.ClearContents
    Range("A15").Select
    Range("H11").Value = Range("H11").Value + 1


I am looking for a way to Clear all cells on a speadsheet after a given row
.. For instance: clear all data below row 1000. In particular, I am lookinfg
for a way to do it with out programatically determinig the LAST row that has
data and having to cycle thru each row and delete it. Is there a line of
code that would basically say: Range. Rows( 1001) to Rows("End of spread
sheet data" ).Clear. I dont want to force it to create the sheets row limit
(row 16000+) either, I just want it to know where the last row data is in.
Any assistance would be helpful. Thanks to All

I'm using a block of code that was written by someone else to perform some calculations.
This is a list of the variables created by the original authors of the program.

Dim RADIUS(9) As Double
Dim INTERFACE(7) As Double
Dim WDTH(8) As Double
Dim AREA(9) As Double
Dim Index As Integer
Dim T1(8) As Double, R1(8) As Double, U1(8) As Double, T2(8) As Double, R2(8) As Double, U2(8) As Double
Dim A1(8) As Double, A2(8) As Double, A3(8) As Double, B1(8) As Double, B2(8) As Double, B3(8) As Double
Dim R(7, 1) As Double, F(7, 1) As Double
Dim E1(7, 7) As Double
Dim E2(7, 7) As Double
Dim A4(8) As Double, T3(8) As Double
Dim E As Double 'Youngs Modulus (MPa)
Dim RHO As Double 'Density (Lbs/in^3)
Dim POISSONR As Double 'Poisson's Ratio
Dim RPM As Double 'Rotations Per Minute
Dim PERIPHP As Double 'Peripheral Pressure (KSI)
Dim BOREP As Double 'Bore Pressure (KSI)
Dim W As Double 'Rotational Speed (Radians)
Dim WOSPED As Double 'Over Speed (Radians)
Dim N As Double 'Centrifugal Stress Coefficient
Dim A5 As Double
Dim T4 As Double
Dim OS As Double
Because I ported this code into my spreadsheet, I suspect that there's some way that these get reset each time the code is run. Somehow, that 'reset' isn't working.
Is there a way to clear all variables after each iteration?

Thanks,
-gshock

Hi All,

This programming is getting the better of me. I currently have workbook with various worksheets.

I then have a main page with all of my parameters on which contains multiple checkboxes designed to take you to each associated worksheet when ticked. This works fine.

When I tick one of the checkboxes, it then takes me to the worksheet using an assigned macro, once on the worksheet is active, I have a two togglebuttons on each particular worksheet.

The first togglebutton has simple print function which will open the print dialogue box..u can image the rest ofcourse.

The second toggleproblem is where my issue is occuring. I have named the second togglebutton return. It uses a simple line of code

, This also works fine.

This is what the current function looks like:
Private Sub ToggleButton2_Click()
    Sheet14.Activate
End Sub
What I would like to add to the code in the above example is that once Sheet14 is activated, I then also want it to clear all the checkboxes on that page or set their values back to 0 or clear?

Is there anyone that can help me with adding something simple that will work?

Thanks in advance,
Skelvissie

Hi,

I Need a Macro To Clear All Content (Not Delete Rows) in a Column Except a List.

List Data
America
Africa
India
Japan

Work Sheet Data
India
America
England
Africa
China
Japan

As per List England and China Not Exist. Hence This Cell Should Be Cleared. It Should come like this
India
America

Africa

Japan

Thanks in Advance.

I need a Macro for a button to clear all contents except row 1 headers from sheets 2-9 with sheet 1 being the master that should never be cleared.

I am creating a construction estimating spreadsheet that has a userform that pulls data about materials from another workbook which the user can select from and create a material list in the estimate. It was created in Excel 2007 and works fine there, but has encountered a problem when run in Excel 2003. Allow me to explain the setup and what the intent is. BTW, like many others, I am new to VBA and am thankful for any help provided.

The estimating spreadsheet has a button that opens a userform with 2 listboxes, 4 buttons, and 1 spinbutton. The 4 buttons are "Add Item", Insert Item", "Delete Item", and "Exit". The spinbutton is used to move the active cell up and down the estimating spreadsheet within a range in column B. When the userform first opens, the first listbox, "MaterialsDBListBox", populates with the worksheet names from another workbook, "Materials database.xls". Upon clicking one item (worksheet name) in MaterialDBListBox, it then populates the second listbox, "MaterialsListBox", with a list of materials from that worksheet and the corresponding data, units, $/unit, etc. for each item. The user can then click one of the materials items in MaterialsListBox to highlight it and then click the "Add Item" or "Insert Item" button to add the selection to the estimating spreadsheet. The user can also use the spinbutton to move up and down that list and use the "Delete item" button to remove an item or place the active cell where they want to insert the item into the estimating spreadsheet.

This works fine as created in 2007, but has several problems in 2003. In 2003, the spinbutton and delete button work when they are the first thing clicked in the userform, but if the MaterialDBListBox (first listbox) is clicked, the "Delete Item" button and spinbutton no longer work. When the user clicks MaterialDBListBox, it populates MaterialListBox (second listbox) as it should. The user can then click to highlight an item in MaterialListBox and click the "Add Item" or "Insert Item" buttons, but nothing happens until the UserForm is closed, and then the item will be added or inserted to the estimating spreadsheet.

I would, obviously, like all the buttons to work all the time. And I would like the estimating spreadsheet to have the selected item added or inserted upon clicking the corresponding button without having to close the userform. As I mentioned, all of this works with the code shown below in 2007, but not in 2003.


	VB:
	
 UserForm_Initialize() 
     ' open the source workbook as ReadOnly
    Set SourceWB = Workbooks.Open("E:TPC TemplatesJoes Excel TemplatesMaterials database.xls", _ 
    False, True) 
     
     ' Populate 1st Listbox with database worksheet names - type of material
    For Each ws In ActiveWorkbook.Worksheets 
        MaterialDBListBox.AddItem ws.Name 
    Next ws 
    SourceWB.Close False ' close the source workbook without saving changes
End Sub 
 
Private Sub MaterialDBListBox_Click() 
    Dim ListItems As Variant, i As Integer 
    Dim SourceWB As Workbook 
    With Me.MaterialListBox 
        .Clear ' remove existing entries from the listbox, turn screen updating off,
         ' prevent the user from seeing the source workbook being opened
        Application.ScreenUpdating = False 
         ' open the source workbook as ReadOnly
        Set SourceWB = Workbooks.Open("E:TPC TemplatesJoes Excel TemplatesMaterials database.xls", _ 
        False, True) 
        UserSheet = MaterialDBListBox.Text 
        UserRange = ("MaterialsDB") 
         
        Dim lbtarget As MSForms.ListBox 
        Dim rngSource As Range 
         
         'Set reference to the range of data to be filled
        Set rngSource = Worksheets(UserSheet).Range(UserRange) 
         
         'Fill the listbox
        Set lbtarget = Me.MaterialListBox 
        With lbtarget 
             'Determine number of columns
            .ColumnCount = 5 
             'Set column widths
            .ColumnWidths = "100;0;0;50;50" 
             'Insert the range of data supplied
            .List = rngSource.Cells.Value 
        End With 
         
        SourceWB.Close False ' close the source workbook without saving changes
        Set SourceWB = Nothing 
         
    End With 
     
End Sub 
 
Private Sub AddItemButton_Click() 
     'Adds row above the add item button
    If MaterialListBox.Text = "" Then 
        MsgBox "Please choose an item from the list" 
        Exit Sub 
    Else 
         'Insert a row, then copy and paste formats and formula
        ActiveSheet.Range("AddMaterialButton").Select 
        Dim row As Single 
        row = ActiveCell.row 
        Selection.EntireRow.Insert 
        Rows(row + 1).Copy 
        Rows(row).Select 
        On Error Resume Next 
        Selection.PasteSpecial Paste:=xlPasteAll 
         
         'Clear contents after copy and paste
        ActiveSheet.Range("A" & row).Select 
        Selection.ClearContents 
        On Error Goto 0 
        Application.CutCopyMode = False 
        ActiveSheet.Range("B" & row).Select 
         'Enter Data from Listbox into Detail Sheet
        With ActiveSheet 
            .Range("B" & ActiveCell.row).Value = Me.MaterialListBox.Column(0) 
            .Range("C" & ActiveCell.row).Value = Me.MaterialListBox.Column(1) 
            .Range("D" & ActiveCell.row).Value = Me.MaterialListBox.Column(2) 
            .Range("E" & ActiveCell.row).Value = Me.MaterialListBox.Column(3) 
            .Range("F" & ActiveCell.row).Value = Me.MaterialListBox.Column(4) 
        End With 
    End If 
     
End Sub 
 
Private Sub SpinButton1_SpinUp() 
    If ActiveCell = Range("MaterialTop") Then 
        Exit Sub 
    Else 
        ActiveCell.Offset(-1, 0).Select 
    End If 
End Sub 
Private Sub SpinButton1_SpinDown() 
    If ActiveCell = Range("AddMaterialButton") Then 
        Exit Sub 
    Else 
        ActiveCell.Offset(1, 0).Select 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Also, lastly, this line of code here:

	VB:
	
ActiveSheet.Range("B" & row).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which appears in the "Add Item" code was put in as a workaround for another problem. After clearing the cell in column A (which holds quantities) when creating a new line for data and copying formulas, I had to change the active cell to a cell that was not empty because the spinbutton does not work when it is on an empty cell, even in 2007. Not sure why?

Thank you!

I have at least over 100 option buttons ranging from "Option Button 1" thru "207" but not in sequential order. Would somebody please help me with a code to clear all the buttons? I tried a code which works only if the Button# is in sequential order (1 thru 10) without any skips. This is in regular excel not in user form.
Any help would be very much appreciated.
Thank you!

I have a worksheet set up where users can enter data to get results.
How do I set up a button to click which will clear all the input
cells?

Jonathan

--
Jonibenj
------------------------------------------------------------------------
Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
View this thread: http://www.excelforum.com/showthread...hreadid=470075

Hello!

How do I add a feature to my function button that clears away data to submit a warning to the user before it clears any data?

It can say something like:
"Are you sure you want to clear all Inventory data?"
With an option, "Yes" and "Cancel"

Here is the current code for the "Clear" button:

Sub Clear()
'
' Clear Macro
'

'
    Range("C7:E91").Select
    Range("C7").Activate
    Selection.ClearContents
    ActiveCell.Select
End Sub
Thank you!

Hello

I was wondering if anyone could help please.

I have a sheet that i wish to clear the contents what i want it to do is just clear the whole sheet when i press the button located on another page.

Could anybody help at all please?

My other problem is, i am copying data over from another source and i am copying by ctrl a and crtl c, this is for simplicity, but it copies a lot of data that i dont want. So what i would like to know is the code for deleting a numbers of rows and columns. i am not sure what rows and columns need to be deleted, but if i have the code, i could just input them at a later date.

Any help would be really appreciated and if any more information is needed please ask

Kind regards

Joe

I have a worksheet set up where users can enter data to get results. How do I set up a button to click which will clear all the input cells?

Jonathan

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?

Excel:
Is there any way to clear all cell entries EXCEPT formulas so that a
spreadsheet that needs to have new entries every month can be cleared easily
for the new month without having to work around the formulas when clearing
the cells?

I want to use a control (button) to click that will clear the sheet but not
clear the formulas and calulation cells.

I'm putting together a time sheet that can used to calculate total hours worked by department etc. I plan on protecting the worksheet and only allowing certain cells to be selected and data entered.

Is there a way using VBA to place a button on the first spreadsheet that will clear all cells that are 'unlocked'?