Need to delete all text in cells with time format in them??

[daniels012]

I have a timesheet that I protect. I would like to write code that will:
Unprotect the sheet then...
delete all the times in cells that have borders.

Is this even possible?

Michael

 

[daniels012]

I have seen this for cells that are yellowed.

can I apply borders instead of color???

Sub ClearYellowCells() 
Dim cl As Range 
    Sheets("Worksheet").Unprotect 
    For Each cl In ActiveSheet.UsedRange 
        If cl.Interior.ColorIndex = 6 Then cl.MergeArea.ClearContents 
    Next cl 
    Sheets("Worksheet").Protect 
End Sub

Michael

 

[agihcam]

the question is what linestyle the border was? is it any?

 

[jindon]

What is you r version of excel and how are the questioned cell formated?

 

[daniels012]

jindon,
Excel 2000 is the version

agihcam,
I used the line style that you use in the formatting toolbar. At least that is how they were created if that is what you mean.

Michael

 

[jindon]

We still need how are the cell formatted?

Need to know NumberFormat property like "hh:mm:ss"....

 

[Jaafar Tribak]

See if this work for you :

place this in a standatd module and run the Test Procedure (It assumes the Cells with borders are in Sheet1 )

Option Explicit

Sub ClearTimesInCellsWithBorders(Sheet As Worksheet)

    Dim oCell As Range
    
    For Each oCell In Sheet.UsedRange.Cells
    
        If HasBorder(oCell) Then
            If IsDate(oCell) Then
                oCell.ClearContents
            End If
         End If
     Next oCell

End Sub


Function HasBorder(Cell As Range) As Boolean

    Dim oBorder As Border

    For Each oBorder In Cell.Borders
        If oBorder.LineStyle <> -4142 Then
        HasBorder = True
        Exit For
    End If

    Next


End Function



Sub Test()

    Sheet1.Unprotect
    Call ClearTimesInCellsWithBorders(Sheet1)
    Sheet1.Protect
    
End Sub

Regards.

 

[daniels012]

This is the code I use to enter the time in 15 minute intervals:

Sub AddTime() 
    ActiveSheet.Unprotect 
    If ActiveCell = "" Then 
    ActiveCell = Time 
    ActiveCell.Value = WorksheetFunction.Round(ActiveCell.Value * 1440 / 15, 0) * (15 / 1440) 
    ActiveCell.Locked = True 
    ActiveSheet.Protect 
    Else 
    MsgBox ("Time has already been entered") 
    End If 
End Sub

Michael

 

[daniels012]

HOw about active sheet versus, Sheet 1??

Michael

 

[daniels012]

bump