Data Validation and a Clear Button

[mrhilder]

Hi,

I'm not very knowledgeable on codes and all that intails. What I'm
trying to do with Microsoft Excel is create a spreadsheet using data
validation. I've gotten that part complete. Now what i'm trying to
do is add a "Clear" button so that the drop down data validation cells/
menus will remain intact but the clear button will turn the cells
blank so that the input user will be able to start over with the drop
down menus. Hope this makes sense. Thanks for any help.

 

[Dan R.]

Open the control toolbox and add a command button to your worksheet.
Then right-click your sheet tab, hit View Code and add this:

Private Sub CommandButton1_Click()
Cells.ClearContents
End Sub

 

[Dave Peterson]

Record a macro when you select the range of cells to clear
then edit|clear|contents (or hit the delete key)
Then stop recording.

 

[mrhilder]

Open the control toolbox and add a command button to your worksheet.
Then right-click your sheet tab, hit View Code and add this:

Private Sub CommandButton1_Click()
   Cells.ClearContents
End Sub

--
Dan

On Dec 31, 11:05 am, (e-mail address removed) wrote:

Dan, thanks for helping...I'm still not getting it to work. Here is the entire "Code" that I have now and its still showing errors, hopefully you canshow me whats wrong.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Private Sub CommandButton1_Click()
Cells.ClearContents
End Sub

End Sub

Also, once this works is this code designed to clear all my cells or
just the ones with Data validation? Thats all I'm trying to do is
clear them not my titles, sub headings etc. Thanks a million

 

[Dave Peterson]

First,
Cells.clearcontents
will clear all the cells on the worksheet. I'm not sure that's what you want!

Second, you don't want the other stuff--just this:
Private Sub CommandButton1_Click()
me.range("a1,b3,d3,e9:f10").ClearContents
End Sub

Change that a1,b3,d3,e9:f10 stuff to just the addresses that should be cleared.

 

[mrhilder]

First,
Cells.clearcontents
will clear all the cells on the worksheet.  I'm not sure that's what youwant!

Second, you don't want the other stuff--just this:
Private Sub CommandButton1_Click()
   me.range("a1,b3,d3,e9:f10").ClearContents
End Sub

Change that a1,b3,d3,e9:f10 stuff to just the addresses that should be cleared.










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Ok, no errors after I inserted that code. However now when I go to
"click" on the command button, my pointer turns into a fourway arrow
and it just wants me to "move" the button not actually press it to
clear. Any help with this problem?

 

[Dave Peterson]

Make sure you have that code behind the worksheet that owns that commandbutton.

And make sure that you've toggled Design mode off.

It's a button on that same control toolbox toolbar.

 

[Dave Peterson]

And make sure you've allowed macros to run--you may need to change the security
setting and/or answer the prompt when the workbook is opened.

 

[mrhilder]

And make sure you've allowed macros to run--you may need to change the security
setting and/or answer the prompt when the workbook is opened.








--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for all the help!!!