Reset Button

**BJbeans** · 06-11-2012, 12:57 PM

Hi, I have a column of 36 cells that users can enter percentages into (whatever percentage they want), after they are done, I would like to just have a reset button that will return all cell values to zero.

I put an ActiveX command button on the page, I just don't know the right VBA syntax to use in the button's Private Sub.

If someone could help me out that would be great! Thank you!

**singerj6** · 06-11-2012, 01:05 PM

Try this,

Sub Reset()

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "0"
    Selection.AutoFill Destination:=Range("A1:A36"), Type:=xlFillDefault
    'Modify above for your range of 36 cells
    
End Sub

Modify for your range of cells as noted.

**Kelshaer** · 06-11-2012, 01:05 PM

Use this and replace the A with the column name

Range("A1:A36") = 0

**BJbeans** · 06-11-2012, 01:24 PM

Thanks singerj6 - that pretty much works, is there a way to right the autofill part so that it only copies the values though? There is a border above my first value so right now its copying that down. Thanks!

**royUK** · 06-11-2012, 01:21 PM

I would use a Forms button, they are more reliable and assign the macro to it.

You con't need to select any cells either

Option Explicit

Sub Reset()
Range(Cells(1, 1), Cells(36, 1)).Value = 0
End Sub

**BJbeans** · 06-11-2012, 01:33 PM

Roy - that seems like maybe a simpler way to do it. I tried doing what you had said but it doesn't seem to be working though. Where should I be entering that code? Just create a new macro in that worksheet or what?

Thanks!

**Kelshaer** · 06-11-2012, 03:12 PM

@BJbeans
Did u try my code?

**BJbeans** · 06-13-2012, 02:26 PM

Roy - I ended up using your code and it works well, now I have an added tweak though that I'm trying to figure out.

Instead of tying the macro to a button, I'd like to just key it off of a cell in the worksheet. I've enabled the cell at the top of my column for data validation to be a list, lets call it cell B1. I'd like to make the list 2 options, "Column Name", and "Reset". When you toggle to "Reset" I'd like that to run the macro.

I've tried to write an If function in VBA around your code - If Range(Cells(1,2)).Value = "Reset" Then...run your code, but this doesn't seem to be working. Any advice?

Thanks!