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: 1839
  

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: 

 

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

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




Tagged: