| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 | |||
Hybrid Mode
