Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Cell Macro
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Cell Macro
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 MESTRELLA29 wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Cell Macro
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" wrote: 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 MESTRELLA29 wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Validation Cell Macro
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)? MESTRELLA29 wrote: 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" wrote: 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 MESTRELLA29 wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro help | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |