Looping Through Sheets Clearing Cells

[austenr]

The attached file has selected cells that need to be cleared at the time of open. All sheets are protected with no password. In module 7 the routine to clear the sheets one at a time by first unprotecting and then clearing the selected cells and then protecting the sheet is located. Right now it stops after clearing the first sheet. Thanks to firefytr for a lot of his help on this project. Why does it stop after the first sheet?

[Jacob Hilderbrand]

Try this

Public Sub ClearSelectedCells()
Dim ws As Worksheet
Dim obj As OLEObject
For Each ws In Worksheets
Select Case ws.Name
Case "GROUP PROFILE"
ws.Unprotect
ws.Range("B5:B14,B20:B22,B26:B32,B38:B45,B49:B52,F1,F5:F7,F10:F11").ClearContents
ws.Protect
Case "ELIGIBILITY GUIDELINES"
ws.Unprotect
ws.Range("B7:B15,B17,B21:B36,F1,F5:F6,F9:F10").ClearContents
ws.Protect
Case "CLASSES AND BENEFITS"
ws.Range("A9:E37,G9:J37,K9:L37,E1:I2,L2").ClearContents
Case "COBRA"
ws.Unprotect
ws.Range("B17:B20,B25:B28,C4:C5,C7,C9:C13,C31:C36,E17:E20,G18,H4:H5,J15").ClearContents
ws.Protect
For Each obj In ws.OLEObjects
If Left(obj.Name, 8) = "CheckBox" Then
obj.Object.Value = 0
End If
Next obj
End Select
End Select
End Sub

[austenr]

Tried to run it and it abends with a "For without Next" error. Compiles o.k. though. ??

[Jacob Hilderbrand]

Typo. Try this:

Public Sub ClearSelectedCells() 
Dim ws              As Worksheet 
    Dim obj             As OLEObject 
For Each ws In Worksheets 
        Select Case ws.Name 
        Case "GROUP PROFILE" 
            ws.Unprotect 
            ws.Range("B5:B14,B20:B22,B26:B32,B38:B45,B49:B52,F1,F5:F7,F1  0:F11").ClearContents 
            ws.Protect 
        Case "ELIGIBILITY GUIDELINES" 
            ws.Unprotect 
            ws.Range("B7:B15,B17,B21:B36,F1,F5:F6,F9:F10").ClearContents 
            ws.Protect 
        Case "CLASSES AND BENEFITS" 
            ws.Range("A9:E37,G9:J37,K9:L37,E1:I2,L2").ClearContents 
        Case "COBRA" 
            ws.Unprotect 
            ws.Range("B17:B20,B25:B28,C4:C5,C7,C9:C13,C31:C36,E17:E20,G1  8,H4:H5,J15").ClearContents 
            ws.Protect 
            For Each obj In ws.OLEObjects 
                If Left(obj.Name, 8) = "CheckBox" Then 
                    obj.Object.Value = 0 
                End If 
            Next obj 
        End Select 
    Next
End Sub

[austenr]

O.K. I could not get it to get past the first sheet it kept abending with a protected sheet error. So, I took out all the protect, unprotect and tried it. This time it clears the first sheet correctly, does not clear the second sheet, clears the third sheet correctly and abends on the forth sheet saying that it cannot clear part of a merged cell. I am attaching what I changed it to to get it to work so far. Getting closer. I figured if worse comes to worse I can do a protection loop after clearing the contents of the sheets. Thanks for your help to this point.

[Jacob Hilderbrand]

Merged cells will cause problems and should not be used. Use Center Across Selection instead to avoid these issues.

Format | Cells | Allignment

[austenr]

Any idea why it skips the second sheet?

[austenr]

I also reformatted the cells to center across selection, same error.

[Jacob Hilderbrand]

Any idea why it skips the second sheet?

The second sheet is named "ELIGIBILITY GUIDELINES" with TWO spaces seperating the words. The code has the name with ONE space.

[Jacob Hilderbrand]

I also reformatted the cells to center across selection, same error.

Are there any merged cells? First unmerge them then Center Across Selection.

[Zack Barresse]

You can use the Merged Cells if you'd like, use the MergeArea method, e.g. Range("A1:E1").MergeArea.ClearContents - this is assuming a range of A1:E1 is merged.


Btw, you'll need to refer to all of your merged cells in one reference, and all others in another, e.g. Range("A1:E1,C4:E4,J15:L15").MergeArea.ClearContents and Range("B1:B4,J2,B20:B23").ClearContents.

[mdmackillop]

Hi Austen,
How about clearing the all unlocked cells (if this is appropriate)
MD

Public Sub ClearSelectedCells() 
For Each ws In Worksheets
For Each cel In ws.UsedRange.Cells
cel.Select
On Error Resume Next
If cel.Locked = False Then cel.ClearContents
If cel.MergeCells Then cel.MergeArea.ClearContents
Next cel
Next ws
End Sub