Select Case in a Change Event

[Risky Dave]

Hi,

I am trying to use a worksheet Change Event to trap changes to specific
columns. depending on which column is chnaged by the user, the value in
another ciolumn will be altered; so, changes in either column O or P will
update column Q, changes in either column T or U will update column V and
changes in column Z or AA will update column AB.

What I can't figure out is the Select Case bit (where I've got the shouted
question below). Here's what I've put together, any comments would be much
appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Application.Intersect(Target, Range("o,p,t:t,u:u,z:z,aa:aa")) Is
Nothing Then
Select Case 'WHAT GOES IN HERE?
Case Is = "o,p"
Range("q" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("o" & Target.Row), Range("p" &
Target.Row))
Case Is = "t:t,u:u"
Range("v" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("t" & Target.Row), Range("u" &
Target.Row))
Case Is = "z:z,aa:aa"
Range("ab" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("z" & Target.Row), Range("aa" &
Target.Row))
End Select
End If
Application.EnableEvents = True
End Sub

TIA

Dave

 

[Dave Peterson]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("o,p,t:t,u:u,z:z,aa:aa")

'single cell at a time
If Target.Cells.Count > 1 Then Exit Sub

If Application.Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Column
'you could use
'Case Is = 15, 16
'but I find that difficult to translate.
'I'd use:
Case Is = Me.Range("o1").Column, Me.Range("p1").Column
Me.Range("Q" & Target.Row).Value _
= Sheets("tables").Range("b9") _
.Offset(-Me.Range("o" & Target.Row).Value, _
Me.Range("p" & Target.Row).Value)

case is = ....

End Select

Application.EnableEvents = True

End Sub


I qualified your ranges (Me.Range()) and added some properties (.Value).

 

[Mike H]

Hi,

I never tested you statements but this should take care of the select case
bits

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("o,p,t:t,u:u,z:z,aa:aa")) Is
Nothing Then

Select Case Target.Column
Case Is = 15, 16
Range("q" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("o" & Target.Row), Range("p" &
Target.Row))
Case Is = 20, 21
Range("v" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("t" & Target.Row), Range("u" &
Target.Row))
Case Is = 26, 27
Range("ab" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("z" & Target.Row), Range("aa" &
Target.Row))
End Select
End If
Application.EnableEvents = True
End Sub

Mike

 

[Ryan H]

Try this. Hope this helps! If so, let me know, click "YES" below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Application.Intersect(Target, Range("O:O,P,T:T,U:U,Z:Z,AA:AA")) Is
Nothing Then
Select Case Target.Column
Case 15 To 16
Range("Q" & Target.Row).Value = _
Sheets("tables").Range("B9").Offset(-Range("O" &
Target.Row), Range("P" & Target.Row))
Case 20 To 21
Range("V" & Target.Row).Value = _
Sheets("tables").Range("b9").Offset(-Range("T" &
Target.Row), Range("U" & Target.Row))
Case 26 To 27
Range("AB" & Target.Row).Value = _
Sheets("tables").Range("B9").Offset(-Range("Z" &
Target.Row), Range("AA" & Target.Row))
End Select
End If

Application.EnableEvents = True

End Sub

 

[Risky Dave]

Had to be something simple - ,my thanks

Hi,

I never tested you statements but this should take care of the select case
bits

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("o,p,t:t,u:u,z:z,aa:aa")) Is
Nothing Then

Select Case Target.Column
Case Is = 15, 16
Range("q" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("o" & Target.Row), Range("p" &
Target.Row))
Case Is = 20, 21
Range("v" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("t" & Target.Row), Range("u" &
Target.Row))
Case Is = 26, 27
Range("ab" & Target.Row).Value =
Sheets("tables").Range("b9").Offset(-Range("z" & Target.Row), Range("aa" &
Target.Row))
End Select
End If
Application.EnableEvents = True
End Sub

Mike

 

[Mike H]

Your welcome, glad I could help

Had to be something simple - ,my thanks

 

[Ryan H]

Good Point, Dave!
--
Cheers,
Ryan

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToInspect As Range

Set myRngToInspect = Me.Range("o,p,t:t,u:u,z:z,aa:aa")

'single cell at a time
If Target.Cells.Count > 1 Then Exit Sub

If Application.Intersect(Target, myRngToInspect) Is Nothing Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Column
'you could use
'Case Is = 15, 16
'but I find that difficult to translate.
'I'd use:
Case Is = Me.Range("o1").Column, Me.Range("p1").Column
Me.Range("Q" & Target.Row).Value _
= Sheets("tables").Range("b9") _
.Offset(-Me.Range("o" & Target.Row).Value, _
Me.Range("p" & Target.Row).Value)

case is = ....

End Select

Application.EnableEvents = True

End Sub


I qualified your ranges (Me.Range()) and added some properties (.Value).