VBA: using range to cycle through pivot items

[Joe_L]

Hi everyone - I currently have a macro where I loop through a range of cells to select items in a pivot table. My problem is if I mistype or fat finger an entry in my range, it overwrites whatever pivot item is in the pivot table and gives me mislabled and usually inaccurate data.

My question is, is it possible to tell my macro to return an error/stop running rather than overwrite the pivot item?

I've searched for this extensively and have been unable to find so I may just need to figure something else out, but figured I'd ask myself to see if anybody might have an idea.

I'm using excel 2007. Here is an general example of the current coding:

'VARIABLES AND LABELS

Dim pt As PivotTable
dim report as long
Set pt = Sheets("Pivot Sheet").PivotTables(1)

Dim rangename1 As String
Dim rangename2 As String

'PIVOT TABLE SELECTIONS

Dim queryct As String
Sheets("Range Sheet").Select
queryct = Application.WorksheetFunction.CountA(Range("A:A"))

For report = 2 To queryct Step 1

rangename1 = Sheets("Range Sheet").Cells(report, 4) & ""
rangename2 = Sheets("Range Sheet").Cells(report, 5) & ""

pt.PivotFields("Pivot Item 1").CurrentPage = rangename1
pt.PivotFields("Pivot Item 2").CurrentPage = rangename2

Thanks as always-
Joe

 

[GlennUK]

Are you wanting to loop through all items of a PivotTable?

 

[Joe_L]

Hi Glenn - right now this macro selects whatever pivot item I type into a data range on another tab so it's not usually every pivot item. My problem is if I mistype anything it overwrites the pivot item names when it tries to select. Just wondering if there is a way to prevent this from happening.

 

[Joe_L]

Ensuring that data validation is not deleted

ok, I gave up on the above idea and just validated each column in the data range using lists. Each column (C to Z) has a different list referenced on another tab. I tried to modify the below seemingly common code within the worksheet to prevent the lists to be overwritten by pasting. Problem now is it seems to work for only 1 column. If I try to apply to the entire range (C2:Z500) I get an "out of stack space" error in Visual Basic and then my workbook crashes. Anybody ever experience this before? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("C2:D500")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[Joe_L]

I managed to find a solution toying around with the code, and since I've seen this question out there but never found the answer in my searches I'll go ahead and post this up incase anybody like me stumbles on it - and as usual for a novice like me, it's really simple.

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("C2:C500")) And HasValidation(Range("D2:D500")) And HasValidation(Range("E2:E500")) And etc..... Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[GlennUK]

Thanks for posting that back.