Sheet_change is not worked

[justuptou]

Dear all,

Can anyone help me urgently ?

I set a table sheet and database sheet

1) Database sheet is talking about

Element 1 column A (key word)
Element 2 column B (number of information)
Element 3 column C, D, E.... (Return information under element A)

2) Table Sheet is set sheet_change VBA

If column B input anything and match Element 1 then
Column D will return the information

3) It is worked after I test the code and saved it. I finished my work on that day through this file.

But when I opened the file on next day,
And input any information on Column B in ""table sheet"
The VBA has not been run automatically.

I have checked on web, someone set Ctrl G and set something but its still not worked.

I have created new file and completed the same code again, but it still not worked on the next day.

[georgiboy]

Hi there, can you paste the code you have?

[Paul_Hossler]

That usually means that a workbook set Application.EnableEvents = False

Open the Immediate Window (Control-G) and ?Application.EnableEvents to see if it's = True

Check Personal.xlsm if you have one, and any other workbooks that might be loaded to see if they're turning it off and forgetting to turn it back on

[SamT]

Check that the Excel File type is *.xlsm or *.xlsb, but NOT *.xlsx

Check that the Workbook, if the correct file type, is macro enabled.

[justuptou]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ENDEND

Application.EnableEvents = True

If Len(Target) <= 0 Then
Exit Sub
End If

Dim Haz(30) As String

    
If Target.Column = 2 Then ' Do it when it is Column number 2
If Target.Row > 3 Then ' Do it when start from Row number 4
If Len(Target.Value) > 0 Then
Sheets("Form").Select
               
Sheets("Hazard").Activate
Sheets("Hazard").Range("A2").Select
Do While Len(ActiveCell.Value) > 0
If ActiveCell.Value = Target.Value Then
HazNum = ActiveCell.Offset(0, 1).Value
For N = 0 To HazNum - 1
Haz(N) = ActiveCell.Offset(0, N + 2)
Next N
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Form").Select
Target.Select
If HazNum > 0 Then
For N = 0 To HazNum - 1
ActiveCell.Offset(N, 5).Value = Haz(N) 'Copy to the Column 'G' (5)
Next N
ActiveCell.Offset(HazNum + 1, 0).Select
End If
End If
End If
End If
    
Exit Sub

ENDEND:

Exit Sub

End Sub

[Paul_Hossler]

Get rid of

On Error GoTo ENDEND

and see where it fails

I'm sure your macro is running EXACTLY the way it's programmed


Since your macro involves several sheets, I think it'd be better to post a sample workbook since we don't even know what sheet the Change event is on

For example, if the macro is on the Form worksheet, there's no need to activate another sheet and select a cell. Possibly that's confusing things


Also you can us the [#] icon to insert CODE tags and paste your macro in between them to set it off and format