Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to test several columns for changes when entering data
I have tried the code below, but it does not seem to work. Some times I get
a type mismatch on the first "IF" statement, then other times it will show the first two msgbox. What I am trying to do is to test for different column changes, and then based on what column, run an event. example. For column A, I want to copy code from another WS, Column BD, the set the formating of the cell after a PASTE There are about 6 columns I need to check. Thanks Bruce Private Sub worksheet_change(ByVal rngTarget As Range) If Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) Then Application.EnableEvents = False irow = ActiveCell.Row temp = MsgBox(irow) icolumn = ActiveCell.Column temp = MsgBox(icolumn) Select Case Cells(rngTarget.Column, 2).Value Case 1 MsgBox "columnA" Case 3 MsgBox "test" Case 6 MsgBox "testf" Case 57 MsgBox "Column BD" Case Else End Select Else 'do nothing - the cell we need to watch hasn't been changed End If Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to test several columns for changes when entering data
I would guess that
Cells(rngTarget.Column, 2).Value should be Cells(2,rngTarget.Column).Value and for the first IF statement If Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) Then should be If Not Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) is nothing Then -- Regards, Tom Ogilvy "Bruce" wrote: I have tried the code below, but it does not seem to work. Some times I get a type mismatch on the first "IF" statement, then other times it will show the first two msgbox. What I am trying to do is to test for different column changes, and then based on what column, run an event. example. For column A, I want to copy code from another WS, Column BD, the set the formating of the cell after a PASTE There are about 6 columns I need to check. Thanks Bruce Private Sub worksheet_change(ByVal rngTarget As Range) If Intersect(Range("A:A, C:C, F:F, BD:bd"), rngTarget) Then Application.EnableEvents = False irow = ActiveCell.Row temp = MsgBox(irow) icolumn = ActiveCell.Column temp = MsgBox(icolumn) Select Case Cells(rngTarget.Column, 2).Value Case 1 MsgBox "columnA" Case 3 MsgBox "test" Case 6 MsgBox "testf" Case 57 MsgBox "Column BD" Case Else End Select Else 'do nothing - the cell we need to watch hasn't been changed End If Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem with test to columns feature identifying odd delimiting character | Excel Discussion (Misc queries) | |||
test data in two columns, return totals | Excel Worksheet Functions | |||
TEST for items in 2 columns to MATCH? | Excel Discussion (Misc queries) | |||
Entering subtotals quickly in several columns | Excel Programming | |||
Sum one column after capmaring test from teo more columns | Excel Worksheet Functions |