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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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 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 Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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 Roger

Ignore last reply - now sorted.
Thanks.

You're a star

John

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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

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 Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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

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?

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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

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


--
Regards
Roger Govier

"jazzman" wrote in message
...
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?

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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

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


"Roger Govier" wrote:

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


--
Regards
Roger Govier

"jazzman" wrote in message
...
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?

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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

Whilst no protection is infallible in Excel, you could protect the Workbook
in the VBE
ToolsVBA Project PropertiesProtectionLock Project for ViewingPassword

--
Regards
Roger Govier

"jazzman" wrote in message
...
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


"Roger Govier" wrote:

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


--
Regards
Roger Govier

"jazzman" wrote in message
...
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?

"Roger Govier" wrote:

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.
--
Regards
Roger Govier

"johnsail" wrote in message
...
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