Get Data button in Pivot Table Wizard

[Forge]

Hello:

I have a Pivot Table (with the password saved) that can be accessed by
number of users.

The users should not have access to the back end (tables,
restrictions, etc.) that gives the final result.

Currently, if the user clicks on the Pivot Table Wizard, they can
click on the "Back" button and then they have access to the "Get Data"
button which allows them to change the query.

I need to disable / hide / inactivate the "Get Data" button.

I tried to protect the worksheet (which takes away the users access
from the "Get Data" button) but then the data does not refresh
(niether automatically upon file open nor manually)!!

Please help how I can take care of this.

Thank you all.

Forge

 

[Debra Dalgleish]

You can use a macro to block the Pivot Table Wizard.

Sub LimitPivotWizard()
ActiveSheet.PivotTables(1).EnableWizard = False
End Sub

Not foolproof, but would help with accidental changes.

 

[Forge]

Thank you for your advice - really appreciate it.

So just to clarify - I create the macro in the spreadsheet but how does it
run - in other words will the macro run automatically as the file is opened?

Thanks once again.

 

[David Heaton]

Thank you for your advice - really appreciate it.

So just to clarify - I create the macro in the spreadsheet but how does it
run - in other words will the macro run automatically as the file is opened?

Thanks once again.









- Show quoted text -

Forge,

the downside of this method is that the field list will disappear and
you cant add fields.

Another option would be to put the following in the Worksheet_Activate
sub


Application.CommandBars("PivotTable Context Menu").Controls
(3).Enabled = False
Application.CommandBars("PivotTable").Controls(1).Controls
(3).Enabled = False


Then to get things back to normal put this code in the
Worksheet_Deactivate sub

Application.CommandBars("PivotTable Context Menu").Reset
Application.CommandBars("PivotTable").Reset



hth

regards

David

 

[Forge]

Thank you David - Appreciate your advice - will try it in a little bit.

Regards,

Forge

 

[Forge]

Hi - I now also need to take care of the "Pivot Table Options" option in the
menu drop down. Can you please help with that too?

Thank you for your help.

Regards,

Forge

 

[David Heaton]

Hi - I now also need to take care of the "Pivot Table Options" option in the
menu drop down.  Can you please help with that too?

Thank you for your help.

Regards,

Forge





- Show quoted text -

add this line to the Worksheet_Activate sub

Application.CommandBars("PivotTable Context Menu").Controls("Table
&Options...").Enabled=False

regards

David

 

[Forge]

Hello David:

I have tried to incorporate the change you mentioned but it is not working
(the Table Options menu item is still visible and accessible). Following is
the full macro being used.

Please let me know what I am doing wrong. Thank you.

Private Sub Worksheet_Activate()
Application.CommandBars("PivotTable Context Menu").Controls(3).Enabled = False
Application.CommandBars("PivotTable").Controls(1).Controls(3).Enabled = False
Application.CommandBars("PivotTable Context Menu").Controls("Table
Options...").Enabled = False
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("PivotTable Context Menu").Reset
Application.CommandBars("PivotTable").Reset
End Sub

 

[David Heaton]

Hello David:

I have tried to incorporate the change you mentioned but it is not working
(the Table Options menu item is still visible and accessible).  Following is
the full macro being used.

Please let me know what I am doing wrong.  Thank you.

Private Sub Worksheet_Activate()
Application.CommandBars("PivotTable Context Menu").Controls(3).Enabled = False
Application.CommandBars("PivotTable").Controls(1).Controls(3).Enabled =False
Application.CommandBars("PivotTable Context Menu").Controls("Table
Options...").Enabled = False
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("PivotTable Context Menu").Reset
Application.CommandBars("PivotTable").Reset
End Sub







- Show quoted text -

What is happening? are you getting an error?

If so please let me know what the error is...

If there is no error check you macro security settings. They may be
set to HIGH which means that the macro's wont run at all.

Regards

David

 

[Forge]

Hi:

It just doesn't grey out - no error per say but just not doing as intended.

Plus when I go into some other excel spread sheet the Pivot Table Wizard
stays greyed out for that also. It looks like it is doing for all
spreadhsheets on my pc not just one workbook.

when i open the spreadhseet in Terminal Server environment - the macro wasnt
working for the Pivot Table Wizard either. I had to run it once manually and
then same thing as above.

While trying all combinations i thought of another way to take care of this.
and that is to Protect the Spreadhsheet and then create a macro to unprotect
upon open and do a refresh all and then protect the worksheet again.

I got it to unprotect and refresh but when i try the "protect" code again to
protect the worksheet it gives me an error saying "cannot update a protected
worksheet". following is the code i am trying - can you please help? Thank
you so much for replying to my queries - I am obliged:

Range("A4").Select
ActiveSheet.Unprotect Password:="test1"
ActiveWorkbook.RefreshAll
Range("A4").Select
ActiveSheet.Protect Password:="test1"
Range("A4").Select

Also please advice if this code should be in:

Private Sub Workbook_Open()

Thanks.

Forge

 

[Forge]

Hello:

I found my answer - used the following code to get what I needed - thank you
for your help and pointers - really appreciate it:


Private Sub Workbook_Open()
With ActiveSheet
.Unprotect Password:="test2"
ActiveWorkbook.RefreshAll
End With
End Sub


Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target
As PivotTable)
ActiveSheet.Protect Password:="test2", _
DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True,
AllowInsertingRows _
:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _
AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub