Data Validation and a Clear Button
 

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.

Data Validation and a Clear Button
 

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, wrote:
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.

Data Validation and a Clear Button
 

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



wrote:

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.

--

Dave Peterson

Data Validation and a Clear Button
 

On Dec 31, 12:25*pm, "Dan R." wrote:
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, 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 can show 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

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.- Hide quoted text -

- Show quoted text -

Data Validation and a Clear Button
 

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.

wrote:

On Dec 31, 12:25 pm, "Dan R." wrote:
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, 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 can show 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

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.- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Data Validation and a Clear Button
 

On Dec 31, 1:45*pm, Dave Peterson wrote:
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.





wrote:

On Dec 31, 12:25 pm, "Dan R." wrote:
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, 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 can show 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

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.- Hide quoted text -

- Show quoted text -

--

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?

Data Validation and a Clear Button
 

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.

wrote:

On Dec 31, 1:45 pm, Dave Peterson wrote:
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.





wrote:

On Dec 31, 12:25 pm, "Dan R." wrote:
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, 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 can show 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

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.- Hide quoted text -

- Show quoted text -

--

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

Data Validation and a Clear Button
 

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.

wrote:

On Dec 31, 1:45 pm, Dave Peterson wrote:
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.





wrote:

On Dec 31, 12:25 pm, "Dan R." wrote:
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, 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 can show 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

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.- Hide quoted text -

- Show quoted text -

--

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

Data Validation and a Clear Button
 

On Dec 31, 3:08*pm, Dave Peterson wrote:
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.





wrote:

On Dec 31, 1:45 pm, Dave Peterson wrote:
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.

wrote:

On Dec 31, 12:25 pm, "Dan R." wrote:
Open the control toolbox and add a command button to your worksheet.