How to clear multiple cells of input data in Excel simultaneously

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Nov 9, 2007.

  1. Guest

    Guest 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.
     
    Guest, Nov 9, 2007
    #1
    1. Advertisements

  2. Guest

    Pete_UK Guest

    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.
     
    Pete_UK, Nov 9, 2007
    #2
    1. Advertisements

  3. Guest

    Corey Guest

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

    "sstea" <> wrote in message
    news:...
    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.
     
    Corey, Nov 9, 2007
    #3
  4. Guest

    Guest Guest

    Re: 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
    > news:...
    > 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.
    >
    >
    >
     
    Guest, Nov 9, 2007
    #4
  5. Guest

    Corey Guest

    Re: 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
    news:...
    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
    > news:...
    > 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, Nov 9, 2007
    #5
  6. Guest

    Guest Guest

    Re: 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
    > news:...
    > 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
    > > news:...
    > > 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.
    > >
    > >
    > >

    >
    >
    >
     
    Guest, Nov 9, 2007
    #6
  7. Guest

    Guest Guest

    Re: 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
    > > news:...
    > > 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
    > > > news:...
    > > > 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.
    > > >
    > > >
    > > >

    > >
    > >
    > >
     
    Guest, Nov 9, 2007
    #7
  8. Guest

    Gary Guest

    Re: 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
    > news:...
    > 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
    > > news:...
    > > 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.
    > >
    > >
    > >

    >
    >
    >
     
    Gary, May 2, 2009
    #8
  9. Guest

    Gary Guest

    Re: 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
    > > news:...
    > > 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
    > > > news:...
    > > > 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.
    > > >
    > > >
    > > >

    > >
    > >
    > >
     
    Gary, May 2, 2009
    #9
  10. Guest

    Gary Guest

    Re: 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
    > > > news:...
    > > > 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
    > > > > news:...
    > > > > 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.
    > > > >
    > > > >
    > > > >
    > > >
    > > >
    > > >
     
    Gary, May 2, 2009
    #10
  11. Re: 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
    > > > > news:...
    > > > > 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
    > > > > > news:...
    > > > > > 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, May 2, 2009
    #11
  12. Guest

    Gary Guest

    Re: 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
    > > > > > news:...
    > > > > > 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
    > > > > > > news:...
    > > > > > > 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
    >
     
    Gary, May 2, 2009
    #12
  13. Re: 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
    > > > > > > news:...
    > > > > > > 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
    > > > > > > > news:...
    > > > > > > > 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
     
    Dave Peterson, May 2, 2009
    #13
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Spiketrip

    Keeping cells clear with no data input

    Spiketrip, Feb 7, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    553
    Frank Kabel
    Feb 7, 2004
  2. Guest

    Simultaneously change values in multiple cells?

    Guest, Jul 20, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    208
    Guest
    Jul 20, 2005
  3. Guest

    how do i enter data in multiple sheets simultaneously

    Guest, Mar 15, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    286
    Guest
    Mar 15, 2006
  4. Replies:
    2
    Views:
    462
    Gord Dibben
    Oct 18, 2007
  5. Vulture

    Select and move two NON-ADJACENT Excel cells simultaneously ?

    Vulture, Jul 30, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    6,355
    Otto Moehrbach
    Jul 30, 2009
Loading...

Share This Page