Validation Cell Macro

[Guest]

I have a List where we are updating the material received to our stock,
The list contais a coulum with validation cell where we input ( Order Short,
Order Complete)

i want the macro to activate when each cell in the coulum changes,

Example,

If cell H2 is updated Order complete "do nothing"
if cell h3 is updated Order Short "Run Macro"


Thanks

 

[Dave Peterson]

You could use a worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("H2:H100")) Is Nothing Then
Exit Sub
End If

Select Case LCase(Target.Value)
Case Is = LCase("Order Short")
Call RunMacroNameHere
Case Else
'do nothing
End Select

End Sub

If you want to try...

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window.

Remember to change H2:H100 to the range you want.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you're sharing the workbook with people using xl97, you may want to read
Debra Dalgleish's notes:
http://contextures.com/xlDataVal08.html#Change

 

[Guest]

Here is a skeleton to permit you what to do. The phrases after Case Is =
must each be exactly as they would appear in your column H entries.

Private Sub Worksheet_Change(ByVal Target As Range)
'need help getting this code into your worksheet?
' http://www.jlathamsite.com/Teach/WorksheetCode.htm
' (e-mail address removed) to contact author
'
If Target.Column <> 8 Then
'not in column H (A=1, B=2...H=8...Z=26, etc)
Exit Sub
End If
Application.EnableEvents = False
Select Case Target.Value
Case Is = "Order Short"
'code to execute here or
'call to macro with code to deal
'with Short orders
Case Is = "Order Long"
'code to execute here or
'call to macro with code to deal
'with overages
Case Else ' all not covered above
'do nothing, just leave
'empty of commands
End Select
Application.EnableEvents = True
End Sub

 

[Guest]

OK Thanks,

I saw this macro in your web page but I still do not undurstand the "Target
Value" or i am doing somting wrong because i still can't get it to run.

I Copied the macro as it Is, Istalled a MsgBox as the Macro just to test but
it would not run Yet.

what am I doing wrong. As you can see a have not change a lot>

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("H2:H100")) Is Nothing Then
Exit Sub
End If

Select Case LCase(Target.Value)
Case Is = LCase("Order Short")
Call RunMacroNameHere
Case Else
MsgBox ("AQUI!")

End Select

End Sub

 

[Dave Peterson]

Did you put it behind the correct worksheet?

Did you make a change to something in H2:H100?

Did you read the notes about events and data|validation at Debra Dalgleish's
site (if you're running xl97)?