PDA

View Full Version : Solved: Looping Through Sheets Clearing Cells



austenr
11-19-2004, 06:45 PM
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
11-19-2004, 06:54 PM
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
11-19-2004, 08:16 PM
Tried to run it and it abends with a "For without Next" error. Compiles o.k. though. ??

Jacob Hilderbrand
11-19-2004, 08:24 PM
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
11-19-2004, 08:58 PM
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
11-19-2004, 09:11 PM
Merged cells will cause problems and should not be used. Use Center Across Selection instead to avoid these issues.

Format | Cells | Allignment

austenr
11-19-2004, 09:14 PM
Any idea why it skips the second sheet?

austenr
11-19-2004, 09:22 PM
I also reformatted the cells to center across selection, same error.

Jacob Hilderbrand
11-20-2004, 12:41 AM
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
11-20-2004, 12:42 AM
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
11-20-2004, 02:15 AM
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
11-20-2004, 04:50 AM
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