Disable dragging Data-field in Pivot Table

J

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
 
D

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.
 
J

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 -
Click to expand...

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
 
D

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
 
J

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 -
Click to expand...


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
 
D

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
'===========================
 
J

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 -
Click to expand...

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
 
J

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 -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

pivot table vb code - how do I restrict access to dropdown box? 3
EnableItemSelction on Page Field in 2003 VBA not working 4
Hide all PivotItems except for a specific Item 1
Fill a Listbox with Values from a Pivot Table Field - an Example 0
Macro to Refresh Pivot Table AND Delete Old Items 1
Creating a pivot table of a pivot table 0
Pivot table sum data 1
Help with Pivot Table 9

Top