How to clear multiple cells of input data in Excel simultaneously

G

Guest

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

Pete_UK

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

Pete
 
C

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

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

Guest

Thanks Corey
You rock man



Corey said:
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.
 
G

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
 
G

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?
 
G

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?
 
D

Dave Peterson

Do you have a worksheet named Sheet1 in the activeworkbook?

Change your code to match the name of the worksheet.
 
G

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?
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top