PC Review


Reply
Thread Tools Rate Thread

How to clear multiple cells of input data in Excel simultaneously

 
 
=?Utf-8?B?c3N0ZWE=?=
Guest
Posts: n/a
 
      9th Nov 2007
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.
 
Reply With Quote
 
 
 
 
Pete_UK
Guest
Posts: n/a
 
      9th Nov 2007
Have they heard of the <delete> key? Just highlight the cells and
press it.

Pete

On Nov 9, 12:10 am, sstea <(E-Mail Removed)> 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.



 
Reply With Quote
 
 
 
 
Corey
Guest
Posts: n/a
 
      9th Nov 2007
Does it mean to clear ALL cells in the sheet that are Not Locked ?

"sstea" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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.


 
Reply With Quote
 
=?Utf-8?B?c3N0ZWE=?=
Guest
Posts: n/a
 
      9th Nov 2007
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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.
>
>
>

 
Reply With Quote
 
Corey
Guest
Posts: n/a
 
      9th Nov 2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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.
>
>
>



 
Reply With Quote
 
=?Utf-8?B?c3N0ZWE=?=
Guest
Posts: n/a
 
      9th Nov 2007
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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.
> >
> >
> >

>
>
>

 
Reply With Quote
 
=?Utf-8?B?RHVrZSBDYXJleQ==?=
Guest
Posts: n/a
 
      9th Nov 2007
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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > 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.
> > >
> > >
> > >

> >
> >
> >

 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      2nd May 2009
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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.
> >
> >
> >

>
>
>

 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      2nd May 2009
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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > 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.
> > >
> > >
> > >

> >
> >
> >

 
Reply With Quote
 
Gary
Guest
Posts: n/a
 
      2nd May 2009
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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > 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.
> > > >
> > > >
> > > >
> > >
> > >
> > >

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to clear contents of multiple cells in Excel simultaneously Jaclyn Microsoft Excel Programming 1 5th Jan 2010 06:09 PM
In Excel how do I update cells in multiple sheets simultaneously? kjh1210 Microsoft Excel Misc 1 22nd Apr 2009 11:01 PM
input of single value into multiple cells simultaneously Joe Microsoft Excel Programming 3 3rd Mar 2006 06:25 PM
clear history, not "Clear History" or "Clear Forms" or TypedURLS Nick Windows XP Internet Explorer 4 31st Mar 2004 05:22 AM
Keeping cells clear with no data input Spiketrip Microsoft Excel Worksheet Functions 3 7th Feb 2004 09:12 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:19 PM.