code not working - HELP !!

[Kevin0427]

Sub removeFilters()
ActiveWorkbook.RefreshAll

x = 1
ThisWorkbook.Sheets("Tracking Log").Unprotect Password:="test"
For x = 1 To 19
ThisWorkbook.Sheets("Tracking Log").Range("B14:T14").AutoFilter field:=x, visibledropdown:=False
Next x
ThisWorkbook.Sheets("Tracking Log").Range("B14:T14").AutoFilter field:=7, Criteria1:=1
ThisWorkbook.Sheets("Tracking Log").Protect Password:="test"
End Sub

The above code does not seem to work. This sub is called when the workbook is opened. The part that does not work is the refreshall. The filters all work. If I refresh manually it works but when I open the workbook it does not refresh the connections. ?!!?!?

 

[Kevin0427]

Could be that on workbook_open, there's too much going on, and it's taking time to refresh the links. You could try:

Private Sub Workbook_Open()
DoEvents
        Call removeFilters
End Sub

Hi there. Possibly the refresh is still running, and then when you re-protect it at the end of the code thats when the error occurs? If so, try inserting this line after the refreshall line:

application.CalculateUntilAsyncQueriesDone

Thanks to both of you. I added both of these and the error is gone but now I get a 1004 error on the filter part that I did not get before. The DoEvents code did not seem to make a difference. And the data is not updating.

 

[sykes]

If it is the fact that the workbook just needs more time to finish getting things ready, you could put in a wait command:

Private Sub Workbook_Open()
Application.Wait (Now + TimeValue("0:00:10"))
        Call removeFilters
End Sub

... will delay the removeFilters from being called, by 10 seconds. You could then experiment with more/less delay until it works...

 

[Kevin0427]

If it is the fact that the workbook just needs more time to finish getting things ready, you could put in a wait command:

Private Sub Workbook_Open()
Application.Wait (Now + TimeValue("0:00:10"))
        Call removeFilters
End Sub

... will delay the removeFilters from being called, by 10 seconds. You could then experiment with more/less delay until it works...

The error comes in the removeFilters sub. I will try adding the wait in there instead.

 

[sykes]

...but you've already proved that if the workbook's open for long enough,the code runs without a hitch, so I'd suggest putting the "Wait" in BEFORE calling the filter code.
Either way, let us know...

 

[Kevin0427]

I am getting a 1004 error in this loop in my removeFilters sub.

For x = 1 To 19
    ThisWorkbook.Sheets("Tracking Log").Range("B14:T14").AutoFilter field:=x, visibledropdown:=False
Next x

 

[Kevin0427]

...but you've already proved that if the workbook's open for long enough,the code runs without a hitch, so I'd suggest putting the "Wait" in BEFORE calling the filter code.
Either way, let us know...

I tried both. Neither work. The code never runs when the book is opened. Only if I do it manually or step through the code manually.