# Create a Macro to Reset Cell Values in MS Excel



## barcas84 (Nov 2, 2010)

Hi, I need help to create a Macro. I have to say I've never created one in my life, so I will need everything describe step by step if it's possible

I have a workbook with multiple sheets, all of them are hidden, except the first one, which is the one that people use. The workbook is a quote calculator, it has multiple dropdown lists and multiple formulas.

So I need a buttom that executes a macro that resets all the numerical cells to blank, without deleting the formulas on it, and also resets all the dropdown lists to blank... Is this possible? 

Thanks!


----------



## macropod (Apr 11, 2008)

Hi barcas84,

Why not simply save an empty workbook as a template (.xlt format) anmed, say 'Quotation'? If you save the template to an appropriate templates folder, then, whenever your users want to creat a new quote, they simply select File|New> 'Quotation' and Excel will open a new 'Quotation' workbook for them all ready for use and which they can save with whatever name they like (Excel will default to the workbook (not template) format for this).


----------



## barcas84 (Nov 2, 2010)

That's a good idea, the problem is that the users have to have the workbook open all the time and do quotes really fast... That's why I need something to reset....


----------



## macropod (Apr 11, 2008)

Hi barcas84,

Closing a workbook and opening a new one is fairly quick. And, if you wanted to make this the user's default workbook (which you'd name 'book.xlt' and place in the default startup location), opening a new one would be as easy as pressing Ctrl-N. Using a template also means you can have multiple quotation workbooks open at the same time for side-by-side comparisons.

Having said that, here's a macro you could use to clear the contents of two cell ranges on the active sheet of the current workbook:

```
Sub Reset()
Application.ScreenUpdating = False
Dim oRng As Range
With ActiveSheet
  On Error Resume Next
  For Each oRng In Range("A1:B2").SpecialCells(xlCellTypeConstants)
    oRng.ClearContents
  Next
  For Each oRng In Range("E5:F8").SpecialCells(xlCellTypeConstants)
    oRng.ClearContents
  Next
End With
Application.ScreenUpdating = True
End Sub
```
If you wanted to initialize one (or both) of these ranges with, say, 0, you could change the corresponding:
oRng.ClearContents
to:
oRng.Value = 0

By using the 'SpecialCells(xlCellTypeConstants)' parameter, the macro will only act on cells that don't contain formulae. That means you can safely specify ranges that do include them. However, you wouldn't want to include cells with alpha-numeric contents that should be left alone.

Perhaps the simplest way to implement the code, after adding it to your workbook, is to assign a keyboard shortcut to it. You can do this via Tools|Macros|Macros > select the 'Reset' macro > Options and, in the box next to Ctl+, type 'r'. from now on, pressing Ctrl-r will reset the workbook.


----------



## barcas84 (Nov 2, 2010)

Thank you Macropod, I'm gonna try that and let you know...


----------



## barcas84 (Nov 2, 2010)

Where do I copy that code, in a Module or in the actual Sheet 1...?


----------



## RSpecianjr (Jan 20, 2010)

Hey barcas,

You would copy that into a Module. Great code Macropod!

Regards,

Robert


----------



## barcas84 (Nov 2, 2010)

Perfect! Thank you!


----------



## barcas84 (Nov 2, 2010)

Hey, I actually have a problem, I don't know why, but it's reseting all the cells in the sheet... What am I doing wrong? I just paste the code in a new module, and I hit run, to try it, and it deleted al the cells... I the range I only put two cells, ("C177:C188"), the other Range I deleted it.


----------



## barcas84 (Nov 2, 2010)

I fixed it... I was doing something wrong... How do I do to put just one cell instead of a range?


----------



## barcas84 (Nov 2, 2010)

I'm having a problem. Lets say A1 =A2+A3. A3 is one of the cells that I am reseting, so in A1 I get the error #N/A, even if I reset A1, I still get that error, is there anyway to avoid this?

Thanks!


----------



## barcas84 (Nov 2, 2010)

I think I could fix it if I could put, instead of Value = 0, Value = a word. Do you guys know how to do that?


----------



## barcas84 (Nov 2, 2010)

Never mind I got it. Thank you so much macropod, that code was exactly what I was looking for!!!!!!


----------



## barcas84 (Nov 2, 2010)

Hi, I have a problem, when I protect the sheet the button doesn't work...


----------



## macropod (Apr 11, 2008)

Hi barcas84,

That is to be expected - as you hadn't mentioned the sheet being protected I didn't code for that. To provide for protection, you need something along the lines of:

```
Sub Reset()
Application.ScreenUpdating = False
Dim oRng As Range, Pwd As String, ProtState As Boolean
ProtState = False
Pwd = "" 'Enter your sheet protection password (if you use one) here
With ActiveSheet
  If .Protect = True Then
    ProtState = True
    .Unprotect Password:=Pwd
  End If
  On Error Resume Next
  For Each oRng In Range("A1:B2").SpecialCells(xlCellTypeConstants)
    oRng.ClearContents
  Next
  For Each oRng In Range("E5:F8").SpecialCells(xlCellTypeConstants)
    oRng.ClearContents
  Next
  If ProtState = True Then
    ProtState = True
    .Protect Password:=Pwd
  End If
End With
Application.ScreenUpdating = True
End Sub
```
Note that most of the sub is unchanged - all that's needed is the dimensioning of two more variables and a few extra lines of code at the start and end of the sub.


----------



## barcas84 (Nov 2, 2010)

Hi, I don't know what happened but is not working. With the new code, if the Sheet is protected, if I press the button nothing happens, and if the Sheet is unprotected, if I press it, then it protects the Sheet... but it doesn't reset the values.


----------



## macropod (Apr 11, 2008)

Hi barcas84,

Sorry, my bad. Try this:

```
Sub Reset()
Application.ScreenUpdating = False
Dim oRng As Range, Pwd As String, ProtState As Boolean
ProtState = False
Pwd = "" 'Enter your sheet protection password (if you use one) here
With ActiveSheet
  If .ProtectContents = True Then
    ProtState = True
    .Unprotect Password:=Pwd
  End If
  On Error Resume Next
  For Each oRng In Range("A1:B2").SpecialCells(xlCellTypeConstants)
    oRng.ClearContents
  Next
  For Each oRng In Range("E5:F8").SpecialCells(xlCellTypeConstants)
    oRng.ClearContents
  Next
  If ProtState = True Then
    .Protect Password:=Pwd
  End If
End With
Application.ScreenUpdating = True
End Sub
```
There's only a minor coding change - '.Protect' changed to '.ProtectContents' on line 7 and the deletion of an unnecessary 'ProtState = True' on line 19.


----------



## barcas84 (Nov 2, 2010)

Beautiful! Works perfect!!! Thanks a lot!


----------

