conditional macro auto run

[excelnoob15]

Hi everyone, im new to xl. im trying to make a stocktake and ordering system. I've made 2 columns for stock in and stock out, that add/subtract from the total shelf stock of each item. My first problem was when adding or taking stock, the value would be + or - again every time another cell was changed. So i made a very basic macro that clears the column. I currently have the macros linked to a buttons which have to be pressed after each individual value is entered. What i want to do is make the macro auto run after every time a value is added to a cell in that column.

The in and out columns are M:M and N:N respectively

any help would be great, thanks.

 

[pedie]

Try this code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(False, False) = Range("M1:N6000") Then
' your macro
 
On Error GoTo 0
End If
End Sub

 

[excelnoob15]

Thanks, not sure what that did, lol. But still not getting the result i need.
this is the macro i want to activate if any cell in range m1:n6000 = >0

Sub resetinout()
'
' resetinout Macro
' Macro recorded 28/08/2010 by sskinner
'
'
Range("M7:N258").Select
Selection.ClearContents

Range("M8").Select
End Sub

 

[Peter_SSs]

Try this code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(False, False) = Range("M1:N6000") Then
' your macro
 
On Error GoTo 0
End If
End Sub

??
Won't the blue line always result in an error and therefore 'your macro' would be run on every worksheet change?

 

[excelnoob15]

Nope, still doesnt work... This is what I've got:

Sub resetinout()
'
' resetinout Macro
' Macro recorded 28/08/2010 by sskinner
'
'
Range("M7:N6000").Select
Selection.ClearContents

Range("M8").Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(False, False) = Range("M1:N6000") Then
' resetinout

On Error GoTo 0
End If
End Sub

Any other idea's? Or am i doing something wrong?
Thanks

 

[Michael M]

Hi guys
Shouldn't this

Private Sub Worksheet_Change(ByVal Target As Range)

BE

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Also, your Reset macro could be

Sub resetinout()
'
' resetinout Macro
' Macro recorded 28/08/2010 by sskinner

   Range("M7:N6000").ClearContents
End Sub

So, your reset macro is called every time any cell in range "M1:N6000" is greater than / equal to 0, thereby clearing the data you just input....?????

 

[excelnoob15]

Ok, i tried those changes, but its still not activating the macro.

And yes the pupose of the macro is to clear the cells after the data has been entered. otherwise it keeps getting added on again when i add more stock to the next row. I've got the spreadsheet setup so when a value is entered into M or N it adds or takes from the total stock on shelf. I have the stock level set up with mins and maxes, so that it automaticly fills out an order sheet with what we need.

Thanks everyone for your help and patience.

 

[Michael M]

Where is you macro placed??
It should be in the sheet module, so if you data is in sheet 1, your Worksheet_SelectionChange macro should be in the sheet 1 module

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    If Range("M1:N6000").Value >= 0 Then
         Call resetinout
    End If
End Sub

 

[Peter_SSs]

Nope, still doesnt work...

I was not suggesting anything that would.

I was merely pointing out that pedie's code would be no use at all for determining when to run the macro and when not to.

(I may make a suggestion for some code to use in due course if the problem remains unresolved)