On Feb 3, 2:23*pm, Per Jessen <perjesse...@hotmail.com> wrote:
> There has to be a sheet with no information to delete. I have added an
> error check.
>
> As you asked in your previous post, I have changed the loop, so the
> macro loop through Sheet1:Sheet7
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Range("w96").Value <> 0 Then
> * * Dim sh As Worksheet
> * * Dim cell As Range
> * * Dim c
>
> * * For s = 1 To 7
> * * * * Set sh = Worksheets("Sheet" & s)
> * * * * sh.Unprotect
> * * * * On Error Resume Next
> * * * * c = sh.Cells.SpecialCells(xlCellTypeConstants).Count
> * * * * If Err.Number = 0 Then
> * * * * * * For Each cell In
> sh.Cells.SpecialCells(xlCellTypeConstants)
>
> * * * * * * * * If cell.Locked = False Then
> * * * * * * * * * * cell.Clear
> * * * * * * * * End If
> * * * * * * Next
> * * * * * * sh.Protect
> * * * * End If
> * * * * On Error GoTo 0
> * * Next
> End If
> End Sub
>
> Regards,
> Per
>
> On 3 Feb., 13:33, Steve <instruct...@hotmail.com> wrote:
>
>
>
> > On Feb 2, 8:35*am, Steve <instruct...@hotmail.com> wrote:
>
> > > On Feb 1, 6:05*pm, Per Jessen <perjesse...@hotmail.com> wrote:
>
> > > > Try this one. Note that the sheet has to be unprotected to use the
> > > > SpecialCells function.
>
> > > > Sub ClearUnlockedCells()
> > > > Dim sh As Worksheet
> > > > Dim cell As Range
> > > > Dim pWord As String
>
> > > > For Each sh In ThisWorkbook.Sheets
> > > > * * If sh.Name <> "NotThisSheet" Then
> > > > * * * * sh.Unprotect Password:=pWord
> > > > * * * * For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
> > > > * * * * * * If cell.Locked = False Then
> > > > * * * * * * * * cell.Clear
> > > > * * * * * * End If
> > > > * * * * Next
> > > > * * * * sh.Protect Password:=pWord
> > > > * * End If
> > > > Next
> > > > End Sub
>
> > > > Regards,
> > > > Per
>
> > > Per, thank you very much for your assistance, the sheets are
> > > protected, but without password, so can cover this aspect with simply
> > > unprotecting the sheet and re applying after this macro has run, have
> > > 22 worksheets, but only need to clear data in the first 7,
>
> > > as i read your code please excuse me if i am wrong but this would
> > > unprotect every sheet?
>
> > > would i need to adjust the code to just unprotect Sheet1:Sheet7
>
> > > Just some clarification
>
> > > Again many thanks
>
> > > Steve- Hide quoted text -
>
> > > - Show quoted text
>
> > have used the code below that was provided for me by pers, but am
> > getting an error message
>
> > Run time error '1004'
> > No cells were found
>
> > There are actually unlocked cells in the spreadsheets with information
> > in them that was expecting to be cleared
>
> > any help with this please
> > am using office 2003
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Range("w96").Value <> 0 Then
> > * * Dim sh As Worksheet
> > * * Dim cell As Range
> > * * For Each sh In ThisWorkbook.Sheets
> > * * If sh.Name <> "wages sheet" Then
> > * * * * sh.Unprotect
> > * * * * For Each cell In sh.Cells.SpecialCells(xlCellTypeConstants)
> > * * * * * * If cell.Locked = False Then
> > * * * * * * * * cell.Clear
> > * * * * * * End If
> > * * Next
> > * * * * sh.Protect
> > * * End If
> > Next
> > End If
> > End Sub
>
> > Steve- Skjul tekst i anførselstegn -
>
> > - Vis tekst i anførselstegn -- Hide quoted text -
>
> - Show quoted text -
Per,
Your an absolute star, many thanks for your assistance
works perfectly
Steve
|
Similar Threads
