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

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
I have seen this for cells that are yellowed.

can I apply borders instead of color???

Code:
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
the question is what linestyle the border was? is it any?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
What is you r version of excel and how are the questioned cell formated?
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
We still need how are the cell formatted?

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

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,253
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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 )

Code:
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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
This is the code I use to enter the time in 15 minute intervals:

Code:
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
 

Forum statistics

Threads
1,136,427
Messages
5,675,784
Members
419,585
Latest member
popsin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top