Auto Run not working

[wafs]

Here is the code I wrote and the auto run is not working. Any suggestions on what I'm over looking.

Sub Macro12()
'
' Macro12 Macro
' Macro recorded 4/25/2006 by Wendy Smith
'run the macro did cells change any time a entry is made in a cell in 1244 HT-DOC 12

ThisWorkbook.Worksheets("1244 HT-DOC 12").OnEntry = "DidCellsChange"
End Sub
Sub keycells()
Dim keycells As String
'define which cells should trigger the macro
keycells = "L9:M42"
End Sub
Sub keycellschange()
Dim cell As Object
Dim reportlocation As String
Dim sh As String
Dim strvar As String
Dim p As Integer
Dim m As String

strvar = "N/A"
sh = "1244 HT-DOC 12"
testrange = keycells
p = 7

'if the values in L9:M42 are equal to 0
For Each cell In Range("L9:M42")
p = p + 1
m = p + 1
If cell = 0 Then
reportlocation = "R" + m
Sheets(sh).Range(reportlocation) = strvar
Else
cell = xlNone
End If
Next cell
End Sub

 

[xlbo]

Hi - how exactly are you expecting this to auto run ???

pls explain "OnEntry" as it does not appear in the excel object model as a property or method of a worksheet.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[wafs]

What I want it to do is run this macro in the background so as a vlookup populates, it will automatically run the macro and enter N/A if the vlookup is zero.

I've made a few changes since the last post.

Sub Autpen()
'
' Macro12 Macro
' Macro recorded 4/25/2006 by Wendy Smith
'run the macro did cells change any time a entry is made in a cell in 1244 HT-DOC 12
ActiveWorkbook.RunAutoMacros xlAutoActivate
ThisWorkbook.Worksheets("1244 HT-DOC 12").OnEntry = "DidCellsChange"
End Sub

Sub DidCellsChange()
Dim keycells As String
'define which cells should trigger the macro
keycells = "L9:L42"
'if the activecell is one of the key cells, call the keycellschanged macro
If Not Application.Intersect(ActiveCell, Range(keycells)) Is Nothing Then keyCellsChanged
End Sub

Sub keyCellsChanged()
ActiveWorkbook.RunAutoMacros xlAutoActivate
Dim cell As Object
Dim reportlocation As String
Dim sh As String
Dim strvar As String
Dim p As Integer
Dim m As String

strvar = "N/A"
sh = "1244 HT-DOC 12"
'testrange =
p = 7
p = p + 1
m = p + 1

'if the values in L9:M42 are equal to 0
For Each cell In Range("L9:L42")
If cell = 0 And cell <> ("#N/A") Then 'vlookup is #N/A and is causing an error2042
reportlocation = "R" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "S" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "Y" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "Z" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AF" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AG" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AH" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AI" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AJ" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AK" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AL" + m
Sheets(sh).Range(reportlocation) = strvar
ElseIf cell = ("#N/A") Then
End If
p = p + 1
m = p + 1
Next cell
End Sub

 

[xlbo]

you would use the worksheet_CHANGE event to do this

Target is the keyword referring to the cell that has been changed

Have a look in the FAQs section at the set of FAQs based on understanding automation events in excel

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question