Tweak to clear button code needed

[Jcraig713]

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

 

[Rick Rothstein \(MVP - VB\)]

If I understand your setup correctly, I would think this subroutine would do
what you want...

Sub ClearButton1_Click()
Union(Range("E5:E12"), Range("G3")).ClearContents
Range("G3").Select
End Sub

Rick

 

[sebastienm]

Hi,
Seems like the cells you are trying to clear are already unlocked.
Also, 99% of the time, when you process cells, you do Not need to select
them first.
So your code would be simply:
Range("E5:E12,G3").ClearContents

(no need to re-select or activate any cell since the above code didn't
change the selection)

 

[Jcraig713]

This works great, actually both suggestions worked well. What would you say
would be a suggestion to clear the cells I have spoke to and also reset a
additional range of cells to = a formula? Currently, one set of cells has an
if statement in the cell but the use can insert whatever value they need. I
would like my clear button to clear the cells as originally indicated but
also reset cells G5-G12 to =
=IF(ISBLANK(E5),0,IF(E5="NM",0,IF(E5="CR",0,G$3))) on click.

 

[sebastienm]

One line of code:

Range("G5:G12").Formula =
"=IF(ISBLANK(E5),0,IF(E5="NM",0,IF(E5="CR",0,G$3)))"

Since E5 is a relative addres (and not $E$5) , excel automatically uses:
- E5 in the formula for cell G5
- E6 in the formula for cell G6
- E7 in the formula for cell G7
- ...

 

[Jcraig713]

In relation then to the following code, where would you put the line of code
you suggest. I think I am putting it in the wrong area becuase I am getting
a compile error when I try and place it:

Private Sub ClearButton1_Click()

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

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

Range("G5:G12").Formula =
"=IF(ISBLANK(E5),0,IF(E5="NM",0,IF(E5="CR",0,G$3)))"

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

Next ocell
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Try this subroutine (which uses what Sébastien has posted) instead of the
one you posted...

Sub ClearButton1_Click()
Range("E5:E12,G3").ClearContents
Range("G5:G12").Formula = _
"=IF(ISBLANK(E5),0,IF(E5=""NM"",0,IF(E5=""CR"",0,G$3)))"
Range("G3").Select
End Sub

Note: In case you are comparing the function assignment statement, I doubled
up the internal quote marks from Sébastien's posted code so that VBA could
properly handle them.

Rick

 

[sebastienm]

Sub ClearButton1_Click()
Range("E5:E12,G3").ClearContents
Range("G5:G12").Formula = _
"=IF(ISBLANK(E5),0,IF(E5=""NM"",0,IF(E5=""CR"",0,G$3)))"
Range("G3").Select
End Sub