# Solved: Excel VBA - Worksheet Change Event - Do Nothing



## UMCaneSimon (Oct 16, 2009)

Hi Everyone, 

I did a search but did not find a solution so I thought I'd post a new one. I have column A with Data Validation so that 1 of 2 values can be select: Formula 1 or Formula 2. I have like to change what appears in column B depending what the use selects in column A. I'm using the Worksheet_Change event to accomplish this. 

However, I DO NOT want the code to run when the value in column A is not changed. For example, if A1 already has Formula 1 selected and I reselect Formula 1 in A1, I want no code to be run. I can't figure how to compare "Target" to what is already existing in the cell. 

Something like If Target.value = Currently Selected Cell.value then Exit Sub

Here's what I have so far:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("A:A")) Is Nothing Then

Select Case Target.Cells.Value
Case "Formula 1"
'Do Something

Case "Formula 2"
'Do Something Else 
End Select

Else
Exit Sub

End If

End Sub

Appreciate any help.

Simon


----------



## bomb #21 (Jul 1, 2005)

"If Not Application.Intersect(Target, Me.Range("A:A")) Is Nothing Then ... Exit Sub"

*If Target.Column <> 1 Then Exit Sub* is much cleaner. 

Beyond that, there's some confusion. Though your code has "Do Something/Something Else", you start out with "I like to change what *appears* in column B depending what the use selects in column A".

If you want to change what appears in B according to A, why use code at all when a formula, such as:

*=IF(A1="Formula 1","X",IF(A1="Formula 2","Y",""))*

would do just as well?


----------



## bomb #21 (Jul 1, 2005)

BTW, here's the "I want it to actually do something rather than just display differently in B, but only if target was changed" option. 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
x = Target
T_Add = Target.Address
Application.EnableEvents = False
Application.ScreenUpdating = False
*Application.Undo*
If Range(T_Add) = x Then
Application.ScreenUpdating = True
MsgBox "No change was made."
Else
Range(T_Add) = x
Application.ScreenUpdating = True
MsgBox "Some change was made."
End If
Application.EnableEvents = True
End Sub


----------



## UMCaneSimon (Oct 16, 2009)

Thanks for the quick reply! Let me see if I can explain this a little better:

I decided to go with macros rather than your suggested IF statements because I actually have 4 distinct cases where the formulas are complex VLOOKUP statements.

To simplify the case, imagine column A to only have 2 possible values: 
Formula 1 & Formula 2. 

Column B's formula should update based on what is in Column A.

The problem I have is with the Worksheet_Change event. 

I only want code to run when Column A's value actually changes, i.e.

Formula 1 --> Formula 2
Formula 2 --> Formula 1

However, the worksheet_change event treats everything as a change and runs code. 

Formula 1 --> Formula 1 is treated as the same event change. I want to be able to catch the instances where value in Column A was not changed. That way, I could conceivably insert a Msgbox alerting this. 

I hope this makes more sense. Thanks again for your help.


----------



## UMCaneSimon (Oct 16, 2009)

Sorry about that, I didn't see your second post until I had already responded. Thanks for the help, the code worked. Kudos.


----------

