Button to clear user worksheet

[Jonibenj]

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

[Dave Peterson]

Select all your input cells
Then type a nice name in the namebox (to the left of the formulabar)
(I used InputRng.)

Then you could add a button to clear all those cells that you just named:

option explicit
sub testme()
worksheets("sheet1").range("InputRng").clearcontents
end sub

If you change the input cells (add more???), just delete the name
(insert|name|define dialog) and then recreate the same name.

Jonibenj wrote:
>
> 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

--

Dave Peterson

[Jonibenj]

Hi Dave,

How many cells can be defined under one name? When I select all 257 of the input cells and try to name them, a number of them are dropped off when I hit the enter key. It seems as though there is a limit to the number of cells that can be held under one name.

I am not familiar with macros at all. Where do I enter it? My sheet name is "Info". Do I insert this into the macro where you have written "sheet1"?

Jonathan

[Dave Peterson]

There's a limit on how long all the addresses can be to make up that string that
refers to the range name.

How about this.

Select about groups of 40 cells at a time. Give each selection a nice name
(InputRng1, InputRng2, ..., InputRng7)

Then the code would change to:

option explicit
sub ClearInputRange()
with worksheets("Info")
union(.range("inputrng1"),.range("inputrng2"), .range("Inputrng3"), _
.range("inputrng4"),.range("inputrng5"), .range("Inputrng6"), _
.range("inputrng7")).clearcontents
end with
end sub

If you use more/less names, you'll have to modify the code.

Since you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Then back to excel and show the Forms toolbar.
Drag a button from the Forms toolbar to a nice spot.

I like to put it in row 1 and then freeze row 1 so that it's always visible
(select A2 and then Window|freeze Panes).

Rightclick on the button and choose Assign macro
and assign ClearInputRange to the button.


Jonibenj wrote:
>
> Hi Dave,
>
> How many cells can be defined under one name? When I select all 257 of
> the input cells and try to name them, a number of them are dropped off
> when I hit the enter key. It seems as though there is a limit to the
> number of cells that can be held under one name.
>
> I am not familiar with macros at all. Where do I enter it? My sheet
> name is "Info". Do I insert this into the macro where you have written
> "sheet1"?
>
> Jonathan
>
> --
> Jonibenj
> ------------------------------------------------------------------------
> Jonibenj's Profile: http://www.excelforum.com/member.php...o&userid=17048
> View this thread: http://www.excelforum.com/showthread...hreadid=470075

--

Dave Peterson

[Jonibenj]

Dear Dave,

Thanks for your help, I've worked it out. I think I'll study up about macros - these things are powerful!!

Jonathan