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

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 wrote:
> 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
Contextures
http://www.contextures.com/tiptech.html

On May 29, 11:08 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
wrote:
> 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 wrote:
> > 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
> 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

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 wrote:
> On May 29, 11:08 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
> wrote:
>
>>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 wrote:
>>
>>>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
>>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
Contextures
http://www.contextures.com/tiptech.html

On May 30, 5:05 am, Debra Dalgleish <d...@contexturesXSPAM.com> wrote:
> 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 wrote:
> > On May 29, 11:08 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
> > wrote:
>
> >>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 wrote:
>
> >>>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
> >>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
> 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

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 wrote:
> On May 30, 5:05 am, Debra Dalgleish <d...@contexturesXSPAM.com> wrote:
>
>>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 wrote:
>>
>>>On May 29, 11:08 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
>>>wrote:
>>
>>>>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 wrote:
>>>
>>>>>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
>>>>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
>>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
Contextures
http://www.contextures.com/tiptech.html

On May 30, 7:28 am, Debra Dalgleish <d...@contexturesXSPAM.com> wrote:
> 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 wrote:
> > On May 30, 5:05 am, Debra Dalgleish <d...@contexturesXSPAM.com> wrote:
>
> >>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 wrote:
>
> >>>On May 29, 11:08 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
> >>>wrote:
>
> >>>>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 wrote:
>
> >>>>>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
> >>>>Contextureshttp://www.contextures.com/tiptech.html-Hidequoted 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
> >>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
> 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

On May 30, 7:55 am, Jen <Sige.Goevae...@gmail.com> wrote:
> On May 30, 7:28 am, Debra Dalgleish <d...@contexturesXSPAM.com> wrote:
>
>
>
>
>
> > 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 wrote:
> > > On May 30, 5:05 am, Debra Dalgleish <d...@contexturesXSPAM.com> wrote:
>
> > >>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 wrote:
>
> > >>>On May 29, 11:08 pm, Debra Dalgleish <d...@contexturesXSPAM.com>
> > >>>wrote:
>
> > >>>>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 wrote:
>
> > >>>>>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
> > >>>>Contextureshttp://www.contextures.com/tiptech.html-Hidequotedtext -
>
> > >>>>- 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
> > >>Contextureshttp://www.contextures.com/tiptech.html-Hidequoted 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
> > 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- 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