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 |
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 |