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

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


"Jcraig713" wrote in message
...
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

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)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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

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" wrote:

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)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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

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
- ...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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" wrote:

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)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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

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



"sebastienm" wrote:

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
- ...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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" wrote:

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)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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

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


"Jcraig713" wrote in message
...
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



"sebastienm" wrote:

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
- ...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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" wrote:

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)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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

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
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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



"sebastienm" wrote:

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
- ...
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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" wrote:

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)
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Jcraig713" wrote:

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