Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) Dim myRngToInspect As Range Set myRngToInspect = Me.Range("o:o,p: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). Risky Dave wrote: 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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
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:o,p: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 "Risky Dave" wrote: 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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
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: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 -- Cheers, Ryan "Risky Dave" wrote: 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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
Had to be something simple - ,my thanks
"Mike H" wrote: 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:o,p: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 "Risky Dave" wrote: 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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
Your welcome, glad I could help
"Risky Dave" wrote: Had to be something simple - ,my thanks "Mike H" wrote: 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:o,p: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 "Risky Dave" wrote: 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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in a Change Event
Good Point, Dave!
-- Cheers, Ryan "Dave Peterson" wrote: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myRngToInspect As Range Set myRngToInspect = Me.Range("o:o,p: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). Risky Dave wrote: 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:o,p:p,t:t,u:u,z:z,aa:aa")) Is Nothing Then Select Case 'WHAT GOES IN HERE? Case Is = "o:o,p: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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet-change event in combination with a select case statement | Excel Programming | |||
Change NumberFormat and Cell Fill Colour in SELECT CASE Construct | Excel Programming | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
VBA- Calendar Control Click Event W Specific Select Case requirements | Excel Programming |