VBA to stop Pivot Table refreshing if blank date will ungroup groupings

[daithiboy]

Morning All,

I'm trying to avoid my dozens of pivot tables date groupings being lost in the event of a date being accidentally left blank in the source data.

I've been toying around with the PivotTableBeforeAllocateChanges event but have had no joy, it just doesn't seem to work.

As a test I wrote a macro to replace any blank dates with "0" to prevent the groupings being lost, this works in isolation but doesn't work when I try and use it with the worksheet event above.

I found this on microsoft but can't make it work for me. The only addition I'd like to make is a check of table column "D" to make sure there are no blanks and alert me and allow me to cancel the update if there is, this part is below.

Sub Worksheet_PivotTableBeforeAllocateChanges(ByVal TargetPivotTable As PivotTable, _  ByVal ValueChangeStart As Long, ByVal ValueChangeEnd As Long, Cancel As Boolean)  
Dim UserChoice As VbMsgBoxResult 
UserChoice = MsgBox("Allow updates to be applied to: " + TargetPivotTable.Name + "?", vbYesNo) 
If UserChoice = vbNo Then 
Cancel = True  
End Sub

Any help would be greatly appreciated.

Ta,
Dave

 

[daithiboy]

Anything?!? Any thoughts would be great.

 

[bartmaster]

Hi,
You mentioned your problem starts if the date is being accidently left blank.
What about adding validation to make sure the dates are not blank, if they are then do not refresh pivottables?