Disable dragging Data-field in Pivot Table

[Jen]

Hi There,

I have borrowed this code for 99,99% from Debra's excellent
PivotPower.xla ...
I just modified it a little to suit my purposes .. which it does not
do
I would like to prevent that the Data-field gets de-placed / removed
from the PT.

Is this feasible?

Sub RestrictDataFieldFeatures()
Dim pf As PivotField
Dim pt As PivotTable
Dim ws As Worksheet
On Error GoTo errHandler
Set ws = ActiveSheet
Application.ScreenUpdating = False

If PivotCheck(ws) Then
If Val(Application.Version) < 10 Then
MsgBox "Some features are only available for Excel 2002 and later
versions"
End If
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
With pt
.EnableWizard = False
.EnableDrilldown = False
.EnableFieldList = False
.EnableFieldDialog = False
.PivotCache.EnableRefresh = False
For Each pf In .DataPivotField '.PivotFields
If pf.Name = "Data" Then '<>"Data" Then

With pf
.DragToPage = False
.DragToRow = False
.DragToColumn = False
.DragToData = False
.DragToHide = False
End With
End If
Next pf
End With
Next pt
Else
MsgBox "There are no pivot tables on the active sheet"
End If
exitHandler:
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

Thanks for your assistance!!!
SG

 

[Debra Dalgleish]

The reason I omitted the Data field from the dragging restrictions is
that I found it hung Excel.
If there's only one Data field, the field button won't be named "Data",
and seems to work correctly.

 

[Jen]

The reason I omitted the Data field from the dragging restrictions is
that I found it hung Excel.
If there's only one Data field, the field button won't be named "Data",
and seems to work correctly.












--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

Hi Debra,

Indeed your original code -when removing the "If pf.Name <> "Data"
Then" seems to work ... but as you said it tends to "hang" Excel.

I've another question which is related.
Would it be possible to DISABLE the Selection-possibility for ONLY the
"data"-field?
-Still assuming that there are at least 2 data-items in the PT.

I used your code once again .. though cannot get it to work for my
spec!
Any idea where it goes wrong?

Sub DisableDATAFieldsSelection()

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
On Error GoTo errHandler

Application.ScreenUpdating = False

If Val(Application.Version) >= 10 Then
If PivotCheck(ws) Then
For Each pt In ws.PivotTables

For Each pf In pt.PageFields
If pf.Name = "Data" Then
pf.EnableItemSelection = False
End If
Next

Next pt
Else
MsgBox "There are no pivot tables on the active sheet"
End If
Else
MsgBox "This feature is only available for Excel 2002 and later
versions"
End If

exitHandler:
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler
End Sub

Thanks for your insight Debra!
Love your website too!
SG

 

[Debra Dalgleish]

Thanks for letting me know that you like the site.
You used pagefields in the code where you should have used pivotfields
or visiblefields:

For Each pf In pt.PageFields
should be
For Each pf In pt.VisibleFields

 

[Jen]

Thanks for letting me know that you like the site.
You used pagefields in the code where you should have used pivotfields
or visiblefields:

For Each pf In pt.PageFields
should be
For Each pf In pt.VisibleFields



















--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

Hi Debra,

Did you get it to work?
I changed the code with your adjustment ... to no avail.
The selection dropdown remains on my Data-field. Any other
"workaround"?

Pivot tables always keep reacting a bit mysteriously.
I am using Excel 2003 SP2

SG

 

[Debra Dalgleish]

Do you have Option Explicit at the top of the worksheet? Are you using
the PivotCheck code?
Maybe you could omit that section:

'=======================
Sub DisableDATAFieldsSelection()

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
On Error GoTo errHandler
Set ws = ActiveSheet

Application.ScreenUpdating = False

For Each pt In ws.PivotTables
For Each pf In pt.VisibleFields
If pf.Name = "Data" Then
pf.EnableItemSelection = False
End If
Next
Next pt

exitHandler:
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler
End Sub
'===========================

 

[Jen]

Do you have Option Explicit at the top of the worksheet? Are you using
the PivotCheck code?
Maybe you could omit that section:

'=======================
Sub DisableDATAFieldsSelection()

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
On Error GoTo errHandler
Set ws = ActiveSheet

Application.ScreenUpdating = False

For Each pt In ws.PivotTables
For Each pf In pt.VisibleFields
If pf.Name = "Data" Then
pf.EnableItemSelection = False
End If
Next
Next pt

exitHandler:
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub

errHandler:
GoTo exitHandler
End Sub
'===========================










--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -

Hi Debra,

Option explicit at the top? Check
PivotCheck code? Check

It somehow does not seem to like this function though ... Without it,
the code runs fine indeed!
Thank you sooooo much!
(I am not a real Excel-expert,unlike yourself, especially not on Pivot
tables. But I am glad we (you) got this one disabled ..as Pivot tables
do not retain their formatting this one comes in very handy!)

Thanks again, SG

 

[Jen]

Hi Debra,

Option explicit at the top? Check
PivotCheck code? Check

It somehow does not seem to like this function though ... Without it,
the code runs fine indeed!
Thank you sooooo much!
(I am not a real Excel-expert,unlike yourself, especially not on Pivot
tables. But I am glad we (you) got this one disabled ..as Pivot tables
do not retain their formatting this one comes in very handy!)

Thanks again, SG- Hide quoted text -

- Show quoted text -

Sub DisableDataFieldSelection()

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
On Error GoTo errHandler
Set ws = ActiveSheet
Application.ScreenUpdating = False
If Val(Application.Version) >= 10 Then
If PivotCheck(ws) Then
For Each pt In ws.PivotTables
For Each pf In pt.VisibleFields
If pf.Name = "Data" Then
pf.EnableItemSelection = False
Else
MsgBox "There is (are) no Data-field(s) on the active
sheet"
End If
Next
Next pt
Else
MsgBox "There are no pivot tables on the active sheet"
End If
Else
MsgBox "This feature is only available for Excel 2002 and later
versions"
End If
exitHandler:
Set pf = Nothing
Set pt = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
Exit Sub
errHandler:
GoTo exitHandler
End Sub

The "Set ws = ActiveSheet" was missing ... !
SG