Thread: VBA Stopped Working!

Hi all,

I've got the following piece of code that automates two autofilters (one in D7, one in D8).

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Str As String
Set Rng = Range("D11:D118")
Str = Range("D7").Value
If Target.Address = "$D$7" Then
      Rng.AutoFilter Field:=3, Criteria1:="*" & Str & "*", Operator:=xlAnd
End If

Dim Rngx As Range
Dim Strx As String
Set Rngx = Range("R11:R118")
Strx = Range("D8").Value
If Target.Address = "$D$8" Then
      Rng.AutoFilter Field:=17, Criteria1:="*" & Strx & "*", Operator:=xlAnd
End If

End Sub

Now, this was working fine until yesterday when all of sudden, it's stopped working. I've checked security settings and all the jive, and a number of other macros on the sheet still work fine. I've checked the code a number of times, but cannot see what's wrong.

Can anyone suggest what I might try to get this working again?

TIA,

SamuelT

Hi Samuel

It could be that you've disabled events. Are the other event procedures working?

Check the value of

Application.EnableEvents

If it's False, set it to True.

HTH
lecxe

Hi lecxe,

How would I go about checking this and changing it? My other events also appear not to be working.

Thanks for the response.

SamuelT

Thank VBA Noob,

Unfortunately, still no joy. One thing which might be affected the event (although I'm not sure why) is the below code which is run via a button click when the user first opens the document. The below example is for all users, although there is similar code for each of nine users:

Code:

Sub All()

    Sheets("Refurb 2006").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=10, Criteria1:="<>"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    
    Sheets("Refurb 2005").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=10, Criteria1:="<>"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    
    Sheets("Woolwich Retained").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=10, Criteria1:="<>"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"
    
    Sheets("Refresh 2007").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=11
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=11, Criteria1:="<>"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"


    Sheets("Branch Of The Future").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=10
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=10, Criteria1:="<>"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"

    Sheets("Refresh + Flagship 2008").Select
    ActiveSheet.Unprotect Password:="donottouch"
    Selection.AutoFilter Field:=11
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=11, Criteria1:="<>"
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="donottouch"


    Sheets("2007 Quickview").Select
    
End Sub

Thanks for the assists guys.

SamuelT

Hi guys,

Update: looks like the problem might be actually a problem with the computer - everything works fine except on my PC. The IT guys are checking out the machine.

Thanks for the input, will report back when I know.

SamuelT

Hi,

So - I sorted out this problem. Turned out to be something very simple: for some reason there appeared to have been a conflict between the VBA and the ASAP Utilities add-on. Once I turned ASAP off, everything worked fine.

You live and learn!

Thanks for all the input,

SamuelT