Reset button to clear cells

M

M.Siler

I have a worksheet that have only certain fields the user can enter data
into. It would be nice to have a button at the top of the sheet that would
clear all the user fields. I can't find anything in Excel to do this so I'm
assuming that it's a VBA thing. I'm not so go at Excel VBA so I don't know
how to do this. Also, the fields are single cells, ranges and all over the
sheet.
 
J

John P

If you can do it manually - ie paint all the cells then remove contents AND
you always do it this way you could record a macro which would convert it to
VBA.

Cheers

John
 
F

Frank Kabel

Hi
one easy way: Record a macro while doing this manually and assign this
macro to a button
 
M

M.Siler

I guess the better question is... now that I have the macro recorded and a
button on my sheet how to I get the button to fire the macro?
 
D

Dave Peterson

I'd give that range of all those cells a nice name.

Select the first cell and ctrl-click to keep adding cells to the selection.

then type MyCellsToClear in the namebox (to the left of the formula bar)
and hit enter!!!

then have your macro include something like:

worksheets("sheet1").range("mycellstoclear").clearcontents


then show the Forms toolbar (view|toolbars|forms)

On that toolbar, there's a button icon. Click on that and then draw a box on
the sheet (topleftcorner to bottomrightcorner) where you want the button.

When you let go, you'll be prompted for the name of the macro to assign.

(You can always rightclick on the button and select "assign macro" later if you
want.)

You may want to give the user a little prompt to verify:

Option Explicit
Sub ClearThemAll()

Dim resp As Long

resp = MsgBox(Prompt:="Are you sure", Buttons:=vbOKCancel)

If resp = vbCancel Then Exit Sub

Worksheets("sheet1").Range("mycellstoclear").ClearContents

End Sub
 
M

M.Siler

No good... I did the following:

To draw a button on the worksheet, you will click the button icon in the
toolbar. Click in the worksheet and drag to draw any size button. When you
release the mouse, the "Assign Macro" dialog will appear. You can now select
which macro should run when you click the button.

I got a button, but I never got the "Assign Macro" dialog.

Here what is shown: =EMBED("Forms.CommandButton.1","")

I right click. I select Properties. I try everything and I never see "Assign
Macro" What gives?
 
C

Chip Pearson

If you are using the command button from the Controls toolbar,
you will not have an "Assign Macro" option. Instead, you'll have
a "View Code" option. Select that and put your code in the event
procedure created by View Code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
M

M.Siler

Thanks... That worked nicely!

Dave Peterson said:
I'd give that range of all those cells a nice name.

Select the first cell and ctrl-click to keep adding cells to the selection.

then type MyCellsToClear in the namebox (to the left of the formula bar)
and hit enter!!!

then have your macro include something like:

worksheets("sheet1").range("mycellstoclear").clearcontents


then show the Forms toolbar (view|toolbars|forms)

On that toolbar, there's a button icon. Click on that and then draw a box on
the sheet (topleftcorner to bottomrightcorner) where you want the button.

When you let go, you'll be prompted for the name of the macro to assign.

(You can always rightclick on the button and select "assign macro" later if you
want.)

You may want to give the user a little prompt to verify:

Option Explicit
Sub ClearThemAll()

Dim resp As Long

resp = MsgBox(Prompt:="Are you sure", Buttons:=vbOKCancel)

If resp = vbCancel Then Exit Sub

Worksheets("sheet1").Range("mycellstoclear").ClearContents

End Sub
Click to expand...
 
Top