How to clear multiple cells of input data in Excel simultaneously

[sstea]

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.

 

[Pete_UK]

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

Pete

 

[Corey]

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

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.

 

[sstea]

Yes, that is exactly what I want to do.

 

[Corey]

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....

Yes, that is exactly what I want to do.

 

[sstea]

Thanks Corey
You rock man

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....

Yes, that is exactly what I want to do.

 

[Duke Carey]

sstea -

Change the line of code that reads

c.Value = ""

to

c..clearcontents

 

[Gary]

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

 

[Gary]

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]

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?

 

[Dave Peterson]

Do you have a worksheet named Sheet1 in the activeworkbook?

Change your code to match the name of the worksheet.

 

[Gary]

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]

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.