Pivot Table update

  • Thread starter Thread starter johnsail
  • Start date Start date
J

johnsail

Is it possible to automatically update (refresh data) a pivot table from a
protected sheet as data is entered?

The sheet with the pivot table needs to be protected/hidden.

John
 
Hi John

Add the following code to the worksheet

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect
PivotTables(1).PivotCache.Refresh
ActiveSheet.Protect
End Sub

Copy the Code above
Right click Sheet tab > View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

This is event code which will be triggered automatically.
 
Hi Roger
Not exactly sure where this code should be put and not sure what triggers it.

I explain:
Have sheet 1 that is an expense sheet and contains code to ensure the user
enters data correctly. The sub is Private Sub Worksheet Change.

I have specified a Pivot Table on sheet 2 using the data from sheet 1.

I would like the pivot table to be updated automatically as the user enters
the data on sheet 1.

Sheet 1 and sheet 2 are protected by password.

Does the code go in sheet 1 or sheet 2 or where?

John
 
Roger - you rock! I was able to accomplish refreshing data on a password
protected worsheet that contained a pivot table. Each sheet that contained a
pivot table I entered your routine and it worked. It asked me for the
password in each sheet the first time there was a pivot table change. Excel
only prompted me one time only for the password. After that everything
worked great...thank you...and I thought i knew excel pretty good...I bow
before the master...
 
Roger - I was mistaken on my last response. The sheet refreshes without any
problems while it is protected BUT if anyone chooses to unprotect the sheet,
it unprotects with out the user being prompted for a password.

Can you provide additional visual basic code to remedy the new problem of
being able to unprotect the sheet with out a password?
 
Hi

Jut substitute what your password is for the word "password" in the code

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect Password:="password"
PivotTables(1).PivotCache.Refresh
ActiveSheet.Protect Password:="password"

End Sub
 
Roger - thanks for responding so quickly. Your new programming works - again
you rock! I hope I haven't used up all my question asking but... even though
the sheet is protected and now, with your additional programming of
"password:="password", the users can't go through the tools menu and
unprotect the sheet unless they have a password...they can however right
click on the sheet tab and choose view code. when you do this you can view
the code that you wrote and I input...and of course the password is visible.
I know the chances of some user stumbling or wanting to venture into the
visual basic editor is remote but is there any way of hiding, masking or
password protecting any user from entering the visual basic editor or keeping
the user from seeing what is written in the editor?

jazzman
 
Hi

Whilst no protection is infallible in Excel, you could protect the Workbook
in the VBE
Tools>VBA Project Properties>Protection>Lock Project for Viewing>Password
 

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

Refreshing Pivot Table on a protected spreadsheet tab 1
Refresh Pivot table data in Protected sheet 3
Pivot Table - Changes 5
Pivot table 1
Refresh pivot table on protected sheet 3
Adding to an existing Pivot Table 7
Refresh Pivot table data in protect sheet 2
Refresh order when refreshing all in a workbook 1

Back
Top