Data Validate List Does Not Update from Pivot Table (post refresh)

[LiamMcg]

Hi, I looked through a number of previous posts but wasn't able to find a similar issue,

I've got a simple data validate list pulling from a pivot table, =OFFSET($AC$60,0,0,COUNTA($AC:$AC)-1) $AC$60 is my first row of the pivot table.

I've got a simple macro running that when you select the worksheet the pivot table refreshes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call RefreshMacro
End Sub


The issue I'm finding is when I refresh the pivot table, then select my cell with the drop down list it does not show the current refreshed items UNTIL I select a row just below the original selected row. After I click on any of the other rows, after the initial one (post refresh) it shows the current list.

Any advice?

 

[sykes]

I've got a simple macro running that when you select the worksheet the pivot table refreshes

What you say here isn't quite correct.
As your "refresh" call is placed in the Worksheet_selectionCHANGE event, then the refresh is trying to work each time you select a new cell in the sheet - including your data-validated cell.
I'm guessing that the refresh isn't always happening quickly enough to update your validation list.

I'm guessing that you actually want the refresh to occur when the worksheet becomes active; in this case it should go into the sheet's _activate event:

Private Sub Worksheet_Activate()
Call RefreshMacro
End Sub

... even then, the refresh may not occur sufficiently quickly for the validation list to be updated in time for the user's click. In this case setting the query's "Background Query" property to false, with VBA (or unchecking the tickbox in the query's properties window), may help.

 

[LiamMcg]

@sykes, this worked perfectly with adjust the query. Thank you!

 

[sykes]

Pleasure! Thanks for marking as the solution, and for the feedback.