How to clear multiple cells of input data in Excel simultaneously
 

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.

How to clear multiple cells of input data in Excel simultaneously
 

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.

How to clear multiple cells of input data in Excel simultaneously
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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.

How to clear multiple cells of input data in Excel simultaneou
 

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

How to clear multiple cells of input data in Excel simultaneou
 

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

How to clear multiple cells of input data in Excel simultaneou
 

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