G
Guest
I am trying to use the time and date stamps worksheet event macro from
McGimpsey but running into a few problems.
1. The worksheet I'm using is protected and this code won't run when the
worksheet is protected.
2. I already have a Worksheet_Change function to format cells for upper and
proper case. Do I have to incorporate the time and date stamps worksheet
event macro into my change case macro, if so any suggestions would be greatly
appreciated. Here is a part of the code.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanUp
Application.EnableEvents = False
Select Case Target.Address
Case "$J$4"
Target(1).Value = UCase(Target(1).Value)
Case "$AC$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$H$5"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$H$41"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$AW$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$BP$5"
Target(1).Value = UCase(Target(1).Value)
Case "$AP$7"
Target(1).Value = UCase(Target(1).Value)
Case "$F$7"
Target(1).Value = UCase(Target(1).Value)
Case "$BH$24"
Target(1).Value = UCase(Target(1).Value)
End Select
CleanUp:
Application.EnableEvents = True
End Sub
3. I would like to use the date stamp function for multiple entries(A60 to
A82). Here is what I had for one.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AR60:BX60"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -43).ClearContents
Else
With .Offset(0, -43)
.NumberFormat = "dd mmm yy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
Sorry for the length of this post.
Tim
McGimpsey but running into a few problems.
1. The worksheet I'm using is protected and this code won't run when the
worksheet is protected.
2. I already have a Worksheet_Change function to format cells for upper and
proper case. Do I have to incorporate the time and date stamps worksheet
event macro into my change case macro, if so any suggestions would be greatly
appreciated. Here is a part of the code.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CleanUp
Application.EnableEvents = False
Select Case Target.Address
Case "$J$4"
Target(1).Value = UCase(Target(1).Value)
Case "$AC$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$H$5"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$H$41"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$AW$4"
Target(1).Value = StrConv(Target(1).Value, vbProperCase)
Case "$BP$5"
Target(1).Value = UCase(Target(1).Value)
Case "$AP$7"
Target(1).Value = UCase(Target(1).Value)
Case "$F$7"
Target(1).Value = UCase(Target(1).Value)
Case "$BH$24"
Target(1).Value = UCase(Target(1).Value)
End Select
CleanUp:
Application.EnableEvents = True
End Sub
3. I would like to use the date stamp function for multiple entries(A60 to
A82). Here is what I had for one.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AR60:BX60"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -43).ClearContents
Else
With .Offset(0, -43)
.NumberFormat = "dd mmm yy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
Sorry for the length of this post.
Tim