Events in VBA

[Masha92]

Hi all,

I am trying to run a macro based on a cell value. I inserted the below code in the sheet where the cell exists. The code never runs and I added a msgbox just to double check and it doesnt fire up. Can you please help me out?

Many thanks!
Masha
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet, Exists As Boolean: Exists = False
Application.DisplayAlerts = False
Application.EnableEvents = True
Set Target = Sheets("Ref").Range("W3")
MsgBox "Changed: " & Target.Address




For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Client File" Then
Exists = True
Exit For
End If
Next ws





If Target.Value = 1 And Exists = False Then


Worksheets.Add(after:=Worksheets.Count) = "Client File"

ElseIf Target.Value = 2 And Exists = True Then


Sheets("Client File").Delete
End If









End Sub

 

[Masha92]

Just to add to this. What I am trying to achieve is when the user select an item via a drop down list (sheet A), the reference cell(sheet B) of this drop down list will change and it exists in a different sheet.

So when the user select a new item from the drop down list, I need a macro to be triggered. (Ideally because the reference cell changes)

Thank you all for the help in advance.

 

[DanteAmor]

Just to add to this. What I am trying to achieve is when the user select an item via a drop down list (sheet A), the reference cell(sheet B) of this drop down list will change and it exists in a different sheet.

So when the user select a new item from the drop down list, I need a macro to be triggered. (Ideally because the reference cell changes)

Thank you all for the help in advance.

First we will send the following code in a module, to verify that the events are active:

Sub test()
  Application.EnableEvents = True
End Sub

Now, put the following code in the events of your sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        MsgBox "Changed: " & Target.Address
        Call your_macro
    End If
End Sub

 

[Masha92]

Thank you Dante Amor. After I restarted the excel and ended all processes, it worked.

Thanks though for your help!

 

[DanteAmor]

Thank you Dante Amor. After I restarted the excel and ended all processes, it worked.

Thanks though for your help!

I'm glad to help you. Thanks for the feedback.