Fill Unprotected Cells

[Mr_Adams]

I use this code to replace the contents of any unprotected cell with a "0"

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()
DoEvents
        <SPAN style="color:#00007F">Set</SPAN> WorkRange = ActiveSheet.UsedRange
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> WorkRange
            <SPAN style="color:#00007F">If</SPAN> Cell.Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> Cell.Value = "0"
        <SPAN style="color:#00007F">Next</SPAN> Cell
Unload UserForm1
ActiveWorkbook.Save
Range("C5").Select
MsgBox "The sheet has been cleared " & Chr(13) & _
             "and saved" & Chr(13) & Chr(13) & _
             "Click OK to continue", vbInformation + vbOKOnly, "Clearing Complete"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">
 
Would it be possible to modify the code to do the following?
Any unprotected cell in Column A or Cloumn B = VBNullstring
Any unprotected cell in Column C = "No Changes"
Any unprotected cell in Column D or Cloumn E = "0"

 

[Von Pookie]

Would this work for you?

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range, cell <SPAN style="color:#00007F">As</SPAN> Range

DoEvents

<SPAN style="color:#00007F">Set</SPAN> WorkRange = ActiveSheet.UsedRange

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> cell.Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> cell.Column
            <SPAN style="color:#00007F">Case</SPAN> 1 <SPAN style="color:#00007F">To</SPAN> 2
                cell.Value = vbNullString
            <SPAN style="color:#00007F">Case</SPAN> 3
                cell.Value = "No Changes"
            <SPAN style="color:#00007F">Case</SPAN> 4 <SPAN style="color:#00007F">To</SPAN> 5
                cell.Value = 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> cell

Unload UserForm1
ActiveWorkbook.Save
Range("C5").Select
MsgBox "The sheet has been cleared " & Chr(13) & _
             "and saved" & Chr(13) & Chr(13) & _
             "Click OK to continue", vbInformation + vbOKOnly, "Clearing Complete"

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mr_Adams]

Perfect, Exactly what I was looking for Thank You.

I did realize I need to add something to this and I not sure how.
Clearing the form also involves deleteing some rows.

I use this code to manually delete a row.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
    <SPAN style="color:#00007F">If</SPAN> Selection.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "You cannot delete a row if you" & Chr(13) & _
                  "have multiple cells selected" & Chr(13) & Chr(13) & _
                  "Select one cell and try again", vbCritical + vbOKOnly, "WARNING"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> ActiveCell.Column <> 1 <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "You cannot delete a row from here" & Chr(13) & Chr(13) & _
                  "You must first select a cell in column A" & Chr(13) & Chr(13) & _
                  "Please try again", vbCritical + vbOKOnly, "WARNING"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">ElseIf</SPAN> ActiveCell(-1, 1).Locked = <SPAN style="color:#00007F">True</SPAN> And ActiveCell(2, 1).Locked = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    MsgBox "You cannot delete this row" & Chr(13) & Chr(13) & _
                  "You must always have at least" & Chr(13) & _
                  "one row between the Report Total" & Chr(13) & _
                  "and the Corrected Total", vbCritical + vbOKOnly, "WARNING"
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        x = ActiveCell.Row
        ActiveSheet.Unprotect "elpaso13"
        Rows(x).Delete Shift:=xlUp
        ActiveSheet.Protect "elpaso13", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows _
        :=True, AllowSorting:=True
        ActiveSheet.EnableSelection = xlUnlockedCells
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I know I can't use this code for the clearing but I thought I might be helpful in some way
Is there a way to look a Column A and see if there are multiple unprotected cells between any to protectected cells and if so delete all but one of the rows.

Example
before
A1 Protected
A2 Unprotected
A3 Unprotected
A4 Protected
A5 Protected
A6 Unprotected
A7 Unprotected
A8 Unprotected
A9 Protected

Action
A1 Protected
A2 Unprotected - Delete Row
A3 Unprotected
A4 Protected
A5 Protected
A6 Unprotected - Delete Row
A7 Unprotected - Delete Row
A8 Unprotected
A9 Protected

Result
A1 Protected
A2 Unprotected
A3 Protected
A4 Protected
A5 Unprotected
A6 Protected

Then run the code posted earlier

 

[Von Pookie]

Is there a way to look a Column A and see if there are multiple unprotected cells between any to protectected cells and if so delete all but one of the rows.

This should take care of this part:


<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> test()
<SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

LastRow = [A65536].End(xlUp).Row

<SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> LastRow
    <SPAN style="color:#00007F">If</SPAN> Cells(x, 1).Locked = <SPAN style="color:#00007F">False</SPAN> And Cells(x + 1, 1).Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Cells(x, 1).EntireRow.Delete
        x = x - 1
        LastRow = LastRow - 1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> x

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Mr_Adams]

You are amazing Kristi
I combined the codes and they work brilliantly

Heres is what I used

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Activate()
DoEvents
<SPAN style="color:#00007F">Dim</SPAN> WorkRange <SPAN style="color:#00007F">As</SPAN> Range, cell <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
ActiveSheet.Unprotect "elpaso13"
LastRow = [A65536].End(xlUp).Row

<SPAN style="color:#00007F">For</SPAN> x = 1 <SPAN style="color:#00007F">To</SPAN> LastRow
    <SPAN style="color:#00007F">If</SPAN> Cells(x, 1).Locked = <SPAN style="color:#00007F">False</SPAN> And Cells(x + 1, 1).Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        Cells(x, 1).EntireRow.Delete
        x = x - 1
        LastRow = LastRow - 1
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> x
ActiveSheet.Protect "elpaso13"

<SPAN style="color:#00007F">Set</SPAN> WorkRange = ActiveSheet.UsedRange

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> WorkRange
    <SPAN style="color:#00007F">If</SPAN> cell.Locked = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> cell.Column
            <SPAN style="color:#00007F">Case</SPAN> 1 <SPAN style="color:#00007F">To</SPAN> 2
                cell.Value = vbNullString
            <SPAN style="color:#00007F">Case</SPAN> 3
                cell.Value = "No Changes"
            <SPAN style="color:#00007F">Case</SPAN> 4 <SPAN style="color:#00007F">To</SPAN> 6
                cell.Value = 0
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> cell

Unload ProgressForm
ActiveWorkbook.Save
Range("C5").Select
MsgBox "The sheet has been cleared " & Chr(13) & _
             "and saved" & Chr(13) & Chr(13) & _
             "Click OK to continue", vbInformation + vbOKOnly, "Clearing Complete"

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>