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. ?!!?!?

 

[NdNoviceHlp]

Maybe...

 ThisWorkbook.RefreshAll

HTH. Dave
ps. Please use code tags

 

[NdNoviceHlp]

Maybe...

 ThisWorkbook.RefreshAll

HTH. Dave
ps. Please use code tags

 

[Kevin0427]

Maybe...

 ThisWorkbook.RefreshAll

HTH. Dave
ps. Please use code tags

I have already tried that too. I have no idea what code tags are...

 

[Special-K99]

I have no idea what code tags are...

The word code enclosed in square brackets [ ] to indicate to the forum that the following is VBA code and should be displayed in a different font for readability, as per NdNoviceHlp's post. His VBA is enclosed in code tags and stands out differently from the rest of his post.
You also need to indicate where the VBA ends by placing the word /code in square brackets also.

 

[Kevin0427]

The word code enclosed in square brackets [ ] to indicate to the forum that the following is VBA code and should be displayed in a different font for readability, as per NdNoviceHlp's post. His VBA is enclosed in code tags and stands out differently from the rest of his post.
You also need to indicate where the VBA ends by placing the word /code in square brackets also.

Thank you. Will use in the future.

 

[Kevin0427]

When I step through this code or run it manually it works.

Sub removeFilters()
ThisWorkbook.Sheets("Tracking Log").Unprotect Password:="test"
ThisWorkbook.RefreshAll
x = 1
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
Sheets("Tracking Log").Cells.Locked = True
ThisWorkbook.Sheets("Tracking Log").Protect Password:="test"
End Sub

When I call the sub on workbook open event it does not refresh the data in the connection.

Private Sub Workbook_Open()
        Call removeFilters
End Sub

 

[Kevin0427]

When I step through this code or run it manually it works.

Sub removeFilters()
ThisWorkbook.Sheets("Tracking Log").Unprotect Password:="test"
ThisWorkbook.RefreshAll
x = 1
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
Sheets("Tracking Log").Cells.Locked = True
ThisWorkbook.Sheets("Tracking Log").Protect Password:="test"
End Sub

When I call the sub on workbook open event it does not refresh the data in the connection.

Private Sub Workbook_Open()
        Call removeFilters
End Sub

Message says "The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password."
and nothing happens.

 

[jmacleary]

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

 

[sykes]

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