Run Macro when Cell changes

[Eager2Learn]

Hello,
I have a validation list in column A and I would like to run a macr
when someone changes the value of column A. I imagine it is a chang
event, but I am unsure how to make this happen? Help.

Thanks,
E2

 

[Binzelli]

Hi Eager,

Use the Worksheet_Change event. If you want something to happen when
cell value changes in column A use code like:


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
MsgBox ("Row " & Target.Row & " in column A has changed to value:
& Target.Value)
End If

End Sub

Good luc

 

[Bob Phillips]

In addition ...

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Eager2Learn]

Thanks for the replies! So, if I wanted a nested if for when Column A
"OPEN" do xxxx , or if Column A = "CLOSE" do xxxx, or Column A
"PENDING" do xxxx.

Each of the xxxx's would be a lot of code for things to take place.

How would I code that?

Thanks again!
E2

 

[mudraker]

Eager2Learn


You could use IF statements or SELECT CASE statements

In My example it will only trigger the SELECT CASE commands if th
change is in column A and only 1 cell has been changed - will not ru
the SELECT CASE commands if f the user pastes data into multiple cell



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 And Target.Count = 1 Then
Select Case Target.Value
Case "Open"
' open commnds here
' or call a sub routine
Case "Closed"
' close commnds here
' or call a sub routine
Case "Pending"
' pending commnds here
' or call a sub routine
Case ""
Exit Sub
Case Else
MsgBox "Unknown Text Entry"
End Select
End If
End Su

 

[Eager2Learn]

this worked perfect