Solved: Employee Rota: Worksheet_SelectionChange not working

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

[vba]

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

[/vba]

Hi Steve,
Welcome to VBAX
A very good attempt on an adventurous first step.
1. I think you want a Change event, not SelectionChange
2. Because you are changing values, you then need to prevent the code from running due the code action so add EnableEvents = False (You must reset to True on Exit or in case of error)
3. "Nothing" is used for an Object, for a Value use ="" or test length of cell (If Len(cel) = 0 )
4. Consider Ucase in the Case test to avoid capitalisation errors, although I see you may be using Validation.


[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim IntersectRange As Range
Set MyRange = Range("CODES")
Dim MyCode As String

Application.EnableEvents = False
Set IntersectRange = Application.Intersect(Target, MyRange)
On Error GoTo Exits
If Not IntersectRange Is Nothing Then
MyCode = Target.Value
Select Case UCase(MyCode)
Case "AL", "DO", "S"
If Target.Offset(0, 1).Value = "" Then
GoTo Exits
End If
Target.Offset(0, 1).ClearContents
Target.Offset(0, 2).ClearContents
End Select
End If
Exits:
Application.EnableEvents = True
End Sub

[/VBA]

Thanks for your quick response. I've pasted your code in the Worksheet Change (which is what I meant in my post!), but I get a "Run-time error '1004' : Method 'Range' of object '_Worksheet' failed at the line :
Set MyRange = Range("CODES")

Any further help would be appreciated. I am however, going out for the rest of the day (it's a very hot sunny day here in England - very rare event - so got to make the most of it), so I'll not see any response until tomorrow.

Thanks again.

Steve

Hi Mdmackillop

Sorry! I tested your revision into the wrong version of my spreadsheet. Now I've put it in the correct one, it works fine. I cannot thank you enough. I can sleep easy now and enjoy my day out. Thanks,

Steve