code not working - HELP !!

Kevin0427

Board Regular
Joined
Mar 31, 2016
Messages
69
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. ?!!?!?
 
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
When I step through this code or run it manually it works.

Code:
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.

Code:
Private Sub Workbook_Open()
        Call removeFilters
End Sub
 
Last edited:
Upvote 0
When I step through this code or run it manually it works.

Code:
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.

Code:
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.
 
Upvote 0
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:
Code:
application.CalculateUntilAsyncQueriesDone
 
Upvote 0
Could be that on workbook_open, there's too much going on, and it's taking time to refresh the links. You could try:
Code:
Private Sub Workbook_Open()
DoEvents
        Call removeFilters
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top