Salsasteve
05-31-2009, 05:50 AM
Excel 2000, running under Windows XP
I have designed a rota for my boss which incorporates a code to indicate why a person has no hours entered for that day. I want the Worksheet_Selection Change Event to detect when a code is entered and clear the contents of the two adjacent cells to the right (the start and finish times), so that the user doesn’t have to keep on pressing the delete key for each day that someone is off work.
I’m just a beginner with VBA, but this should be fairly straightforward. I have a named range for the codes called, funnily enough, “CODES”. The Worksheet_SelectionChange event macro, checks if one of the three codes (AL, DO, S) has been entered, and then clears the contents of the two adjacent cells to the right, unless the cells are already empty, in which case it doesn’t need to do this.
I’ve fiddled with it for hours, and had some very strange things happening, and I’m beginning to wonder if the fact that I set up a data validation “drop down” for the user to enter the code might have a bearing on this? I have placed the code in the Worksheet object, SelectionChange procedure list in the VBA.
My code is below. Could anyone offer any help please? Thanks.
Steve
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("CODES")
Dim MyCode As String
Set IntersectRange = Application.Intersect(Target, MyRange)
On Error GoTo SkipIt
If Not IntersectRange Is Nothing Then
MyCode = Target.Value
Select Case MyCode
Case "AL", "DO", "S"
If Target.Offset(0, 1).Value Is Nothing Then
Exit Sub
End If
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
End Select
End If
SkipIt:
Exit Sub
End Sub
I have designed a rota for my boss which incorporates a code to indicate why a person has no hours entered for that day. I want the Worksheet_Selection Change Event to detect when a code is entered and clear the contents of the two adjacent cells to the right (the start and finish times), so that the user doesn’t have to keep on pressing the delete key for each day that someone is off work.
I’m just a beginner with VBA, but this should be fairly straightforward. I have a named range for the codes called, funnily enough, “CODES”. The Worksheet_SelectionChange event macro, checks if one of the three codes (AL, DO, S) has been entered, and then clears the contents of the two adjacent cells to the right, unless the cells are already empty, in which case it doesn’t need to do this.
I’ve fiddled with it for hours, and had some very strange things happening, and I’m beginning to wonder if the fact that I set up a data validation “drop down” for the user to enter the code might have a bearing on this? I have placed the code in the Worksheet object, SelectionChange procedure list in the VBA.
My code is below. Could anyone offer any help please? Thanks.
Steve
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("CODES")
Dim MyCode As String
Set IntersectRange = Application.Intersect(Target, MyRange)
On Error GoTo SkipIt
If Not IntersectRange Is Nothing Then
MyCode = Target.Value
Select Case MyCode
Case "AL", "DO", "S"
If Target.Offset(0, 1).Value Is Nothing Then
Exit Sub
End If
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
End Select
End If
SkipIt:
Exit Sub
End Sub