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.

Duke,

I'm one step closer, but this macro won't run:
I get Run-tme error '9':
Script out of range


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


What's wrong?


"Duke Carey" wrote:

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.

Do you have a worksheet named Sheet1 in the activeworkbook?

Change your code to match the name of the worksheet.

Gary wrote:

Duke,

I'm one step closer, but this macro won't run:
I get Run-tme error '9':
Script out of range

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

What's wrong?

"Duke Carey" wrote:

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.







--

Dave Peterson

Thanks Dave, that was my problem.

However, the macro won't run as I get the Macro Disabled warning asking me
to lower my security level to allow the macro to run. I've done this, but
still can't get past that message.

Any suggestions? I'm also wondering if people I send my worksheet to will
have this same problem and be unable to run my macro. Is there a way to
prevent this also?

"Dave Peterson" wrote:

Do you have a worksheet named Sheet1 in the activeworkbook?

Change your code to match the name of the worksheet.

Gary wrote:

Duke,

I'm one step closer, but this macro won't run:
I get Run-tme error '9':
Script out of range

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

What's wrong?

"Duke Carey" wrote:

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.







--

Dave Peterson

In xl2003 and before (not sure about xl2007), if you've disabled macros when you
opened the workbook, you'll have to close it and reopen it.

And these kinds of security settings are set by the user. If a developer could
change the security, it really wouldn't be too secure.

So...

Depending on each user's setting, they may have the same problem.

Gary wrote:

Thanks Dave, that was my problem.

However, the macro won't run as I get the Macro Disabled warning asking me
to lower my security level to allow the macro to run. I've done this, but
still can't get past that message.

Any suggestions? I'm also wondering if people I send my worksheet to will
have this same problem and be unable to run my macro. Is there a way to
prevent this also?

"Dave Peterson" wrote:

Do you have a worksheet named Sheet1 in the activeworkbook?

Change your code to match the name of the worksheet.

Gary wrote:

Duke,

I'm one step closer, but this macro won't run:
I get Run-tme error '9':
Script out of range

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

What's wrong?

"Duke Carey" wrote:

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.







--

Dave Peterson


--

Dave Peterson