Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 31st 04, 02:24 PM
Saariko
 
Posts: n/a
Default How to lock a worksheet and enable the PivotTable refresh data opt

I guess the subject says it all. I have a pivottable I want to lock the
worksheet it's in. but still keep the refresh data button available.

Which of the functions should do it? I tried them all with no success.

Saariko

  #2   Report Post  
Old October 31st 04, 03:37 PM
tjtjjtjt
 
Posts: n/a
Default

You mean that the worksheet is Protected?
You will likely have to write a macro that Unprotects the Sheet, refreshes
the PivotTable, then Reprotects the sheet. If that's what you are looking for
post back and someone will help you get started.

tj

"Saariko" wrote:

I guess the subject says it all. I have a pivottable I want to lock the
worksheet it's in. but still keep the refresh data button available.

Which of the functions should do it? I tried them all with no success.

Saariko

  #3   Report Post  
Old October 31st 04, 03:55 PM
tjtjjtjt
 
Posts: n/a
Default

Something like this should get you started. Note that the AllowPivotTables
part will only work in Excel 2002 or 2003. Leave it out if you are using 2000
or earlier or if you do not want people to be anble to change the PivotTable
structure.

This macro assumes the password assigned to the Worksheet is 12345. If you
don't have a password, you can leave that part off. Also, the name of your
PivotTable is likely not PivotTable5 (as the one below is). To find the name
of your PivotTable, Right-Click on it and choose Table Options.

tj

Sub RefreshPivotTable()

ActiveSheet.Unprotect 12345
ActiveSheet.PivotTables("PivotTable5").PivotCache. Refresh
ActiveSheet.Protect 12345, AllowUsingPivotTables:=True

End Sub


"Saariko" wrote:

I guess the subject says it all. I have a pivottable I want to lock the
worksheet it's in. but still keep the refresh data button available.

Which of the functions should do it? I tried them all with no success.

Saariko

  #4   Report Post  
Old October 31st 04, 04:02 PM
Celtic_Avenger
 
Posts: n/a
Default


Sub RefreshProtected()
ActiveWorkbook.Sheets("YourSheetName").Unprotect ("YourPassword")
ActiveWorkbook.RefreshAll
ActiveWorkbook.Sheets("YourSheetName").Protect("Yo urPassword")
End Sub

There are other more detailed levels of protection you can add to the
protection code, but this in its simplest form would do what you are
looking for.

You could assign this to an active control button within your sheet.

Celtic_Avenger


--
Celtic_Avenger
------------------------------------------------------------------------
Celtic_Avenger's Profile: http://www.excelforum.com/member.php...o&userid=14101
View this thread: http://www.excelforum.com/showthread...hreadid=273974



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to enable font color on protected worksheet? dlterry Excel Discussion (Misc queries) 3 January 29th 05 12:19 AM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017