Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Create A Clear/reset Button?

I'm just finishing a project that is a collection of three calculators, each of which returns data based on a series of user-supplied variables. All the mechanics are working [thanks in part to replies to previous posts], but there is one more thing I'd like to add:

Does anyone have a way to clear the contents of another cell [actually several] cells via another cell? As the most complex calculator requires eight variables to be entered, I'd like to provide a quick way [a button?] to "clear" or "reset" the calculator - basically wipe all unlocked cells. I should note that all of the cells in question already have Data Validation of one type or another.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Create A Reset Button That Returns Combo Box Values Back To The First One In The List And Clears All Cells At The Same Time
I have 4 combo boxes with selectable options and several cells for inputting data into. I'm looking to create a reset button that returns combo box values back to the first one in the list and clears all cells at the same time. I've figured out the cell reset as follows;

View Replies!   View Related
Reset Memory Or Clear Old Value
How come I keep having the same value for "lastrow". eg: 35

lastrow = ActiveSheet.UsedRange.Rows.Count

It doesn't matter how many rows of data I've changed, it still show lastrow = 35.

I tried to add an extra line above but still doesn't work:

lastrow = 0
lastrow = ActiveSheet.UsedRange.Rows.Count

View Replies!   View Related
Reset / Clear ComboBox
I have a userform with several combo boxes on it. I also have three command buttons, 'OK', 'Reset' and 'Cancel'. I want to code the 'Reset' command button to reset the values of the combo boxes to empty or "". I have attempted to do this simply by using the following code.

Private Sub cmdReset_Click()
cmbWaterTemp.Value = ""
cmbTypeFastIce.Value = ""
cmbIceDrift.Value = ""
cmbTrendBehavior.Value = ""
End Sub

When I click on the 'Reset' command button I get the following error.

"Run-time error '94':
Invalid use of Null"

When I use the debug tool the error pointed to is a different sub.

Private Sub cmbWaterTemp_Change()
txtTemp1Encoded.Text = Left(cmbWaterTemp.value, 1)
txtTemp2Encoded.Text = Right(cmbWaterTemp.Value, 1)
End Sub

I am not exactly sure why the error points to this sub. I know there must be a simple way to accomplish this. I am including a workbook that is similar though not exactly the same due to file constraints. What I want to happen is when the 'Reset' button is clicked is to clear all values for the combo boxes and the text boxes. I have successfully gotten the textbox values to clear although not the combo boxes.

View Replies!   View Related
LookUp Reset Button Anomaly
I have a Lookup that updates name from a list.

D2 =IF(LOOKUP($A$2,B:B,C:C)=",",LOOKUP($A$2,B:B,C:C)) - this Works fine

Say A2=3
C1= Name1
C2= Name2
C3= Name3

Also a macros that if D2 is typed over you can re-insert the LookUp.

Sub Reset()
For Each ce In Range("D12) 'Company
'If IsEmpty(ce) Then
ce.FormulaR1C1 = "=IF(LOOKUP(R2C1,C[-2],C[-1])=",",LOOKUP(R2C1,C[-2],C[-1]))"
ce.Font.ColorIndex = 11
'End If
End Sub

The Weird thing is that in stead of putting the value from the LookUp it paste the formula
=IF(LOOKUP(R2C1,C[-2],C[-1])=",",LOOKUP(R2C1,C[-2],C[-1])) like it was text

I have used the reset on a number of numeric equations no problem, but not on a LookUp one. I've tried changing the format of the cell, but not successfully.

View Replies!   View Related
AutoFilter Status/Reset Button?
A coworker has come to me asking for a way to indicate on a sheet whether or not any autofilters are applied, and a button to reset all the filters. The second part is easy, and I've got a CommandButton with the following code to do it:

View Replies!   View Related
Option Button - Reset To Unchecked
I want to add a secondary function to these buttons; this is a necessary function for every order, so I want to make it necessary to click one of the buttons for every order. One way to do this would be to leave the button unchecked.

How could the button be set to leave itself unchecked after being clicked on?

View Replies!   View Related
Does Clicking A Button Reset Variables?
I've included the code I'm using to calculate the net weight depending on which option button is selected. I use a flag ("VLAG") to know whether it's the first time the button is pressed. All variables are DIMmed at initialization (different sub).

By using other flags, I can see the code runs perfectly, but the tare weight ("TARRA") is not incremented each time the button is pressed. The formula I'm using to increment is

View Replies!   View Related
Button To Reset Combos And Other Cells
I have a spreadsheet with 4 combo boxes and 2 cells that I need reset to a specific value when a button is clicked (the top value for the combo boxes, the function [today()] for one cell, and a specific date for the other). I figured I would put the function and the specific date in seperate hidden cells and the button would link to them, but whatever way gets the job done will work. I know I'll need to code this in VB, but as well as I know excel i've never played with basic before.

View Replies!   View Related
Reset A Textbox In Userform When Button Is Clicked -
I can't find a way to reset/clear the textbox in order to enter new data

View Replies!   View Related
Macro/command Button To Reset All Filters In Pivot Table
I have a pivot table with over 4000 entries and about 12 columns which i filter to get the info i want.

Unfortunately with so many rows and columns in the table, if i do a macro that gets each filter back to 'show all' its really slow.

Doing this manually using the "Show all" option in the filter takes a fraction of a second, so surely there must be a way to do this more quickly in VBA?

i can't understand why manually it takes no time at all, but automating it through recording a macro takes forever.

View Replies!   View Related
Need To Create A Button That Can Copy Cells, Create A New Sheet And Then Paste There
So I've got Sheet 1 with say


I would like to create a button that can create a new sheet and paste A1 to C3 at the same location on the new sheet

and I need this to create a new sheet and do that everytime the button is pressed.....

View Replies!   View Related
Clear Command Button
I want to create a command button that will clear whatever is written in a text box. I guess for this to work it needs to select all, then clear. The command button and the text box have been created using the control toolbox.

View Replies!   View Related
Clear Userform By Button
I have a CLEAR FORM command button

What codes can I use so that when I click CLEAR FORM, all the data that I encoded in the textboxes of my current userform will be erased as if I have a fresh new form?

View Replies!   View Related
Clear Option Button
In ThisWorkbook Userform2 is triggered upon a double click

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

End Sub

Userform2 has Frame1 (Option Buttons 1-6) and Frame2 (Option Buttons 7-12)

Private Sub OptionButton1_Click()

Call ProjectBuildings
End Sub

Private Sub OptionButton10_Click()

Call BondIndustrial

End Sub

The user can click away and things work great. The macros that are triggered search a number of Workbooks and individual Worksheets looking to select either of two (hence the two Frames) specific items.

If the user clicks any Option Button in Frame 1 (works) then any Option Button in Frame 2 (works) then goes back to the same Option Button in Frame 1 (nothing) I have a problem.

How do I clear the first Option Button in Frame 1 after the user selects another Option Button in Frame 2?

View Replies!   View Related
A Button To Clear And Populate Tabs?
I have seen this before in spreadsheets so I atleast know it is possible.

I have a worksheet that has 6 sheets in it. The final sheet is a formula page that only shows results if sheets 2-5 have data. Sheet 1 holds all the master data. Sheets 2-5 show subsets based on sheet 1. I manually atm filter sheet 1 for certain info and then copy paste into 2-5.

Seeing as I do this weekly. How would I go about adding in a 7th tab or even a macro that will delete all data from row3-end in tabs 2-5, then copy the appropriate data from tab 1 based on a set criteria?

I could write this in a macro just wondering if there is an easy built in way?

View Replies!   View Related
Macro Button To Clear Certain Cells
My Workbook with a macro to clear ONLY the highlighted Cells.

The Highlighted Cells can be found on the sheet "Line 5"

View Replies!   View Related
VBA Button To Clear 'unlocked' 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'?

View Replies!   View Related
UserForm 'Clear Entries' Button
I have a button on my userform that I would like to clear all entries in column B in a sheet called "6 Entries" when pressed

Is there a code for this ?

View Replies!   View Related
Button To Clear Excel Cache
I keep encountering an error while creating multiple sheets within the same workbook. This is a known error by Microsoft as documented here. In order to avoid using multiple workbooks i need to clear excels cache each time I run the macro as i have limited users sheet creation to 30 sheets which should not trigger the error. Does any one know a code to clear excels cache or memory?

I have used cutcopymode = false but that does not do the trick.

View Replies!   View Related
Clear UserForm Controls Button
Function FindNth(Table As Range, Val1 As Variant,Val1Occrnce As Integer, _
Val2 As Variant,Val2Col As Integer, ResultCol As Integer)
Dim i As Integer
Dim iCount As Integer
Dim rCol As Range
For i = 1 To Table.Rows.Count
If Table.Cells(i, 1) = Val1 And _
Table.Cells(i, Val2Col) = Val2 Then
iCount = iCount + 1
End If
If iCount = Val1Occrnce Then
FindNth = Table.Cells(i, ResultCol)
Exit For
End If
Next i
End Function

To use this function, push Alt+F11 and go to Insert>Module. Paste in the code, push Alt+Q and save. Use the function as shown in graphic example. You can access it under "User Defined" in the Paste Function (Shift+F3).

Type Amount Sex Cost Purchased
Cat 1 Male $ 5.00 22/05/01
Cat 2 Male $ 5.00 15/00/00
Dog 3 Male $ 20.00 25/04/00
Rat 5 Female $ 1.00 15/08/01..................

View Replies!   View Related
Command Button To Clear Check Boxes
I am trying to create a command button in Microsoft Excel (Office 2000) that enables me to reset the check box status for a worksheet from checked to unchecked. The worksheet contains approximately 50 check boxes and is meant to be reused. Is this possible? If so, how can I program it to do so?

View Replies!   View Related
Save, Save As- And Reset Button
I have a spreadsheet with 32 worksheet, I want to write a macro to "save the work, save as.. in a specific location and with a specific name and then reseting all data in specific cells.

View Replies!   View Related
Create A Button
I have a userform, with a save button.

The Save button transfers data from my userform to a worksheet via variables.

It then proceeds to use the variables to generate further data, dependant on a combination of the variables, as this process creates a row of data on the first available empty row, as this script is being used in a loop, ie add another record yes or no.

I need to incorporate a method whereby the user at any point can click on cell S(x) where (x) corresponds to the row clicked, which will then run a macro which will allow me to change a value from OPEN to CLOSED and also add a userentered date into a field called DATECLOSED, it is imperative that this process only work on the row in question.

View Replies!   View Related
Use A Button To Create New Sheet?
Is it possible to create a button in or out of a cell and when that is clicked it will generate a new sheet in the same workbook.

Now this is throwing the line to you clever people... Having then created a new sheet can it be that in say A:1 it will display the date the sheet was created AND make a popup box appear to allow the user to enter a name for the new sheet?

View Replies!   View Related
Create A Button In Worksheet
creating a button (using VBA code) in a excel worksheet that can initiate a range of output data in a col (say (C1:C100)) by activating the therein formula of each cell (same as what F2 does) and then entering the cell.

Actually I have to do manually each time for each output cell after opening the worksheet that I don't want.

View Replies!   View Related
How To Create Button On Excel Sheet

I have 1 sheet where in sheet 2 i have two type data and that is 1 level service data and 2 level service data. In sheet 1 i wanted 2 buttons that is 1 level service button and 2 level service button. and i wanted that if i will click on 1 level service button it iwll give me the 1 level service data from sheet 2.

View Replies!   View Related
How Do I Create A Button In Xl07 Ribbon For Add In
I have a workbook that has a userform with some simple code to translate formulas from .FormulaLocal to .Formula, and back.

I use that workbook a lot to translate formulas I find on here to something my German XL2007 will understand. I thought it would be neat, as well as giving me the opportunity to learn something new, to have that functionality as an add-in, rather than having the workbook open all the time. It is easy enough to save the wb as an add-in, and I can see it in my add-in list, install or un-install it. What i can't figure out is how to put a button on the xl07 ribbon when the add in is installed so i can access it.

View Replies!   View Related
Create A Button To Print A Sheet
I thought I solved it, but it was on a different workbook. I am not in pagebreak preview and it's not password protected. Why can't I insert an object to create to assign my macro?

View Replies!   View Related
Macro Button To Create New Line?
I have 2 tabs on my spreadsheet. The first has some text at the top in cells A1 and A2, and that's it. The second tab I need a script for. Basically, I need some sort of macro button and a text box. I want people to be able to type some text in a cell or box on the 2nd tab, hit a button, and the text that has just been typed will be copied and pasted in to the next available cell down on the first tab.

For example, say the 1st tab had 'title' written in cell A1, and 'hello' written in cell A2. If I were to go to the 2nd tab, type 'bonjour' in to a cell or text box, and hit a button, the text 'bonjour' would be copied, the script would read the 1st tab and see that cells A1 and A2 already had text in them, and so paste 'bonjour' in to cell A3.

View Replies!   View Related
Create A Find Button In The Spreadsheet
I'd like to create a button that makes the "Find and Replace" popup come up when pressed. How to do this? (Ctrl + F is the shortcut for it)

View Replies!   View Related
Create Command Button And Run From The Ribbon?
Is it true that I have to enable macro and choose to trust access to the VBA project object model in order to create and run macros? It seems there is some security risk associated with making the workbook as macro enabled. What makes the commands run from the ribbon security free? Is it true that we can work around the macro security concern by creating a command button to the ribbon and run the macro from the command button? If yes, how come I could not find any discussion of creating the command buttons? I am using Excel 2007.

View Replies!   View Related
Create New Worksheets From Button/link
on a workbook I use.

The workbook (26-11GL.xls) contains a varying number of sheets.
Sheet 1 - Menu
Sheet 2 - DS1
Sheet 3 -DS2
Sheet 4 -DS3
Sheet 5 - DS4
Sheet 6 - Company1
Sheet 7 - Company2
Sheet 8 - Company3
Sheet 9 ....14 Company 4,5 etc
Sheet 15 - CompanyTotals

What I would like to do is when I open the workbook on the Menu page I would like to have some links / buttons that will automatically create a new worksheet called DS5 which has all the formatting, formulas etc from DS4 (or the last complete DS sheet)
If this is possible I would like to be able to add Company worksheets as both DS sheets and company sheets can have from 3 to 35 sheets in either area.

If possible I would like the new DS sheets to be named with the next valid number.

View Replies!   View Related
Call Tracker - VBA Coding: Input Screen To Auto Clear The Fields Once The " Save " Button
1) We need the Input screen to auto clear the fields once the " Save " button has been pressed and data transfered to the appropriate tracking sheet

2) We want to ensure that if any of the Red cells are empty the " Save " button will give an error ( one complication If the "Transfer Type" highlighted in Blue has an entry " either STD/ISD/ESP " then " Call Purpose " needs to be mandatory

3) somehow the information on the "Comment " field is not getting captured

4) If possible we want a warning indicator if two rows in a tracking sheet ( example London ) are the same based on Coloums B to I ( except C )

5) Is there any possibility to give a pop calender in the " scheduled date " section of Input screen (Row 33)

View Replies!   View Related
Create Excel Report Sheet With VB Button
I have attached the worksheet.

As you can see its just a Job logging spreadsheet, What I am trying to do is create a jobs out standing log that will probably go in place of the Search Results sheet.

I have a job Info sheet that is just for new jobs and a Jobs Done sheet that is for jobs done, in the Search Results sheet I would like to have Jobs Outstanding, this would be done by matching the Job Number in the Job Info & Jobs Done Info sheets and giving me a report on all outstanding Jobs. I would also like to add a Jobs Outstanding Button to the Intro Sheet.

View Replies!   View Related
Create A Button Which Moves The User To Next Page
I wish to create a button which will allow the user to goto the page in my spreadsheet.

I no i can use macros or hyperlinks to do but i can only use this button once for it to work. I want to cut and paste the button to save time.

View Replies!   View Related
Create A Button That Will Change The Color Of ANY Cell
I need to create a button in Excel that will change the color of ANY cell I want it to.

I have had a play around and I can only create a button that changes the color of one particular cell!

Can I edit the range code in VB in order to apply this to any particular cell?

View Replies!   View Related
Create Button In Excel Menu For Macro
I am trying to make this code works by Toggle botten in Mymenu.

when i press the toggle bottun the copy takes place, then move the curser to distenation and press the Toggle again to past only the comment.

How to make the Toggle bottun inside Mymenu (A menu in the Worsheet Menu Bar).

Sub Macro1()
' Selection.Copy
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

View Replies!   View Related
Create Macro/Button That Will Return All Autofilters Back To ALL
I want to have a small button at the top of the sheet that when pressed, will automatically reset all filtered autofilters back to show all.

View Replies!   View Related
Create Button & Assign Macro When Worksheet Added
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?

how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.

View Replies!   View Related
Create An "Insert Row" Button In An Excel Spreadsheet
Can anyone tell me what the macro code is to do this (at whatever box I have selected at the time)

say my box is selected at E3, then when I click the button, it creates a new row E

likewise for F7, AA1, etc...

View Replies!   View Related
Reset To Zero
I have a workbook with 20 worksheets the first is a summary called Discount Set the 19 sheets with various names

The 19 sheets in column H4:H40 u enter a number greater than 0 which is the quanty required. This is used to calculate a price on which ever row it is entered

the selection must only affect numbers as there are rows that are just shading and they contain no data .

So u could enter quanties on serval rows of different sheets to get a total price displayed in the Discount set

What I want to do is have button on the Discount Set which will clear any quantity that does not equal 0 back to 0 in column H which will reset the total 0.00

View Replies!   View Related
Reset VBA Code
When I execute a macro, and if during the macro execution I press the "Esc" key, the execution stops and I get a Microsoft Visual Basic window with the message that code execution has been interrupted with buttons to Continue, End, Debug and Help. If I click the "End" button, the window closes and Excel is at rest - which seems to be some sort of Reset process.

I have a need to execute that process while Excel is at rest.

Is there any way to code the process in VBA?

View Replies!   View Related
Reset Cell Value To Zero
i have cells A1 to A10 filled with numbers
i have cells B1 to B10 blank
what i need to do is when something is typed in cell B1 - A1 to A5 are reset to zero and when i type something in cell B2 - A2 to A6 reset to zero.

I have tried to do this as a range ie

A1- A10 have numbers in B1 - B10 copy these numbers and reset if anything is typed in C1 with this formula =IF(C1:C5="",A1,"0") what it should do is reset B1 to zero if anything is typed in C1-C5 but only when something is typed in C1 does this work ..

View Replies!   View Related
Reset Value Of Long?
I know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.

But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.

I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?

View Replies!   View Related
Reset The Schedule..
Is there a way to take the values between C2 & D2 and have them automatically post in column F ?

View Replies!   View Related
Reset This Sheet
is there anyway i can reset this to start at st001 ive got everything done but i need to reset the orders to start at st001

View Replies!   View Related
Reset Counter
I am not experienced at all with Excel, here is my problem: In column "A": I have 288 rows and has nothing but numbers in them. Row 1 is the lowest number, row 288 is the highest number. In each row the numbers typically increase; every once in a while the numbers may stay the same, but the number will never be lower. The numbers range from 0 through 600,000.

What I want to do is have column "B" follow column "A" until the cell total reaches 12000. The next cell in column "B" would then reset and start all over from "0". I want it to keep on reseting every 12000 counts. Another potential problem is that a majority of the time the cell value will not be an even 12000, 24000, 36000 etc. They may be more like A40: 11742 A41: 13201 etc.

View Replies!   View Related
Reset A Spreadsheet With A Macro
As mentioned I want to create a button that can reset this/a spreadsheet.

I have locked all the cells and unlocked the cells with the light green background. I.e. where a user can input data.

I haven't added a password yet nor protected the sheet. (the macro needs to work when the sheet is protected.)

I am looking for a macro that can be used to reset the sheet to its original state. However, the sheet needs to be protected.

View Replies!   View Related
Reset Number To 0 On Mondays
As before, please ignore the colored portions. They mean nothing. This is what I'm trying to do:

Every Monday the "minimum payment due" should reset to the same

as "payment per session" without affecting the rest of the columns.

This will help us in determining who owes us what, and how many classes they have paid for up-to-date.

View Replies!   View Related
Reset All Enteries On The Worksheet
Is it possible to press a cell on the worksheet to clear all entered data in
the entire worksheet?

I have a worksheet that users enter several data in different pages and then
print a report. The next user will have to clear all eneries and then enter
their own data before printing the next report. At this time, we close the
program and reopen it which is a bit of pain, just wondering if we could
create a key (let call it "Reset all Forms" on the worksheet to press and
clear all data.

View Replies!   View Related
Copyright 2005-08, All rights reserved