filling cells with formulas

[Pierre via OfficeKB.com]

Hi experts,
I have the folowing code which worked fine in the past but now, nothing
happens when changing a cell in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Dim sForm As String
Set myRng = Intersect(Target, Range("D"))
If myRng Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
For Each myCell In myRng.Cells
sForm = "=if(OR(O4=""offerte"",O4=""afgesloten""),if(S4<>"""",S4*U4,0),
0)"
Me.Cells(myCell.Row, "V").Formula = Replace(sForm, 4, myCell.Row)
sForm = "=if(I4<>"""",Vlookup(I4,hulpblad!B2:C250,2),"""")"
Cells(Target.Row, "J").Formula = Replace(sForm, 4, Target.Row)
Next myCell
Application.EnableEvents = True
End Sub

any ideas?
thanks,
Pierre

 

[Norman Jones]

Hi Pierre,

Perhaps events have been disabled. Try:

Alt-F11 to open the VBE
Ctrl-G to access the Immediate window
Type Application.EnableEvents = True
Hit Enter

BTW, if your code changes Application settings, it is good practice to
restore the sttings in am error handler, e.g,:

'=========>>
Sub AAA()

On Error GoTo XIT
Application.EnableEvents = False

'Your code

XIT:
Application.EnableEvents = True
End Sub
'<<=========

 

[Pierre via OfficeKB.com]

Thanks Norman,

You were right !
Just one more question:

Because the cells in which the formulas go are not protect i get a red
triangle with a message with each cell.
Can i turn that off?
Thanks,
Pierre

Hi Pierre,

Perhaps events have been disabled. Try:

Alt-F11 to open the VBE
Ctrl-G to access the Immediate window
Type Application.EnableEvents = True
Hit Enter

BTW, if your code changes Application settings, it is good practice to
restore the sttings in am error handler, e.g,:

'=========>>
Sub AAA()

On Error GoTo XIT
Application.EnableEvents = False

'Your code

XIT:
Application.EnableEvents = True
End Sub
'<<=========

---
Regards,
Norman

Hi experts,
I have the folowing code which worked fine in the past but now, nothing

[quoted text clipped - 23 lines]

thanks,
Pierre