Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Clear Button in excel spreadsheet

  Asked By: Lloyd    Date: Sep 07    Category: MS Office    Views: 2908
  

In my spreadsheet, I have a clear button (coded below). The whole
sheet (all cells) are locked except range E5 - E12 , G3 and G5
through G12 which are unlocked.

What I want to do upon click of the clear button is for the the
cells in E5-E12 & G3 to be cleared leaving the unprotected cell
range G5 through G12 uncleared then of course leaving all other
locked cells alone. What can I add or change in my code to
accomplish this?


Private Sub ClearButton1_Click()

Dim cell As Range
Range("E5:E12", "G3").Select

For Each cell In Selection
If cell.Locked = False Then
cell.ClearContents
End If

Range("G3").Select
Range("G3").Activate

Next
End Sub

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Myron James     Answered On: Sep 07

Sub ClearButton1_Click()
Dim cell  As Range

Range("E5:E12").Select
For Each cell In Selection
If cell.Locked = False Then cell.ClearContents
Next
Range("G3").Activate
Selection.ClearContents
End Sub

 
Answer #2    Answered By: Vidisha Pathak     Answered On: Sep 07

I'm not sure that your use of the locked/unlocked cells
has anything to do with what you're asking.
I think the problems is with the range  selection.
I tried this:
Private Sub ClearButton1_Click()
Dim ocell As Range
Range("G3").ClearContents
For Each ocell In Range("E5:E12")
ocell.ClearContents
Next ocell
End Sub

and it worked just fine.

the way you've defined your range:
Range("E5:E12", "G3").Select
Isn't giving you the results you expect.
it's actually selecting range E3:G12 (try using the debugger to step
through)
And, actually, it isn't strictly necessary to select the cells.

 
Answer #3    Answered By: Barney Smith     Answered On: Sep 07


Thanks, you're right. The locked/unlocked issue was irrelavant.
The revised code  provided works great! What would be your
suggestion if upon clearing the cells  I do and resetting the focus
with the following used (below), lets say that I want to do all that
the code indicates for clearing but also resets cells G5:G12 with
the following formula on click:

=IF(ISBLANK(E9),0,IF(E9="NM",0,IF(E9="CR",0,G$3)))

Original Code:
Private Sub ClearButton1_Click()

Dim ocell As Range
Range("G3").ClearContents

For Each ocell In Range("E5:E12")
ocell.ClearContents

Range("G3").Select
Range("G3").Activate

Next ocell
End Sub

 
Answer #4    Answered By: Bu Nguyen     Answered On: Sep 07

I think the only reason you have to check to see if the cell  is
locked or not is because you are selecting multiple ranges, which is
not possible. If I were to keep it simple, and already know
specifically what cells  I want to clear, I would just do something
like this:

Private Sub ClearButton1_Click()
[E5:E12].ClearContents
[G3].ClearContents
End Sub

 
Answer #5    Answered By: Alonzo Roberts     Answered On: Sep 07

since you're not clearing the "G" cells, the formulas should still be there.
unless the user does something.
If I were you, I'd simply record a macro and insert the formulas.
then stop recording, and copy/paste the macro code  into your click
event after the clearcontents loop.

 
Answer #6    Answered By: Don Snow     Answered On: Jul 06

this should work
=IF(ISBLANK(E9),0,IF(E9="NM",0,IF(E9="CR",0,G$3)))

 
Didn't find what you were looking for? Find more on Clear Button in excel spreadsheet Or get search suggestion and latest updates.




Tagged: