I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values.

Have they heard of the <delete key? Just highlight the cells and
press it.

Pete

On Nov 9, 12:10 am, sstea wrote:
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values.

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values.

Yes, that is exactly what I want to do.

"Corey" wrote:

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values simultaniously.

Use this code

Dim c As Range
For Each c In Sheets("SHEET NAME HERE").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next

It will delete ALL cells values not locked in sheet selected


Corey....

"sstea" wrote in message
...
Yes, that is exactly what I want to do.

"Corey" wrote:

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values simultaniously.

Thanks Corey
You rock man



"Corey" wrote:

Use this code

Dim c As Range
For Each c In Sheets("SHEET NAME HERE").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next

It will delete ALL cells values not locked in sheet selected


Corey....

"sstea" wrote in message
...
Yes, that is exactly what I want to do.

"Corey" wrote:

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values simultaniously.

sstea -

Change the line of code that reads

c.Value = ""

to

c..clearcontents



"sstea" wrote:

Thanks Corey
You rock man



"Corey" wrote:

Use this code

Dim c As Range
For Each c In Sheets("SHEET NAME HERE").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next

It will delete ALL cells values not locked in sheet selected


Corey....

"sstea" wrote in message
...
Yes, that is exactly what I want to do.

"Corey" wrote:

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values simultaniously.

Corey,

GREAT explanation!

I am having a problem assigning this macro to a button. I drag a button onto
my form, then right click on it to see the dropdown, and click on Assign
Macro. There is only one macro listed, which I assume is this one, so I
select it and go back to my form. Then when I click on the macro button, I
get the message "Macro (macro name) not found."

My workbook is named "Quick ROI.xls"

My Sheet1 name is "Quick ROI Questions"

Here is my macro:
Dim c As Range
For Each c In Sheets("sheet1").UsedRange
If c.Locked = False Then
c.ClearContents
End If
Next

And when clicking on the macro button, I get the error message:
The macro "Quick ROI.xls'!OptionsButton24_Click' cannot be found.

What am I doing incorrectly?

Gary

"Corey" wrote:

Use this code

Dim c As Range
For Each c In Sheets("SHEET NAME HERE").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next

It will delete ALL cells values not locked in sheet selected


Corey....

"sstea" wrote in message
...
Yes, that is exactly what I want to do.

"Corey" wrote:

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values simultaniously.

Corey,

I'm one step closer. (You're probably guessing I'm new to macros) I
discovered I must name my macro and end my sub routine (duh), but this macro
won't run:

Sub ClearForm()
Dim c As Range
For Each c In Sheets("sheet1").UsedRange
If c.Locked = False Then
c.ClearContents
End If
Next

End Sub

I get Run-tme error '9':
Script out of range

What's wrong?

"Gary" wrote:

Corey,

GREAT explanation!

I am having a problem assigning this macro to a button. I drag a button onto
my form, then right click on it to see the dropdown, and click on Assign
Macro. There is only one macro listed, which I assume is this one, so I
select it and go back to my form. Then when I click on the macro button, I
get the message "Macro (macro name) not found."

My workbook is named "Quick ROI.xls"

My Sheet1 name is "Quick ROI Questions"

Here is my macro:
Dim c As Range
For Each c In Sheets("sheet1").UsedRange
If c.Locked = False Then
c.ClearContents
End If
Next

And when clicking on the macro button, I get the error message:
The macro "Quick ROI.xls'!OptionsButton24_Click' cannot be found.

What am I doing incorrectly?

Gary

"Corey" wrote:

Use this code

Dim c As Range
For Each c In Sheets("SHEET NAME HERE").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next

It will delete ALL cells values not locked in sheet selected


Corey....

"sstea" wrote in message
...
Yes, that is exactly what I want to do.

"Corey" wrote:

Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" wrote in message
...
I have a data input form in Excel where users enter values into unprotected
cells and view the result in a protected cell. I want the users to be able to
click a "button" to clear all of the previously entered cell input values simultaniously.