How to add PivotTable drop down selector to another tab?

[AdamU]

Anyone know how to keep a worksheet listed in the tabs but prevent anyone without a password from seeing the data?

[BurgoEng]

As far as I know there is no foolproof way of doing this. As you know Excel is not very secure, and it's password protections are fairly easy to crack by anyone with access to the internet. Therefore, if the data is really propriatery, and the people you are hiding it from are determined, anything you do in Excel can be broken into.

I have few work around for **** if you like it you can take it.
a) Select all of the cells on the sheet, and change the background and font colors to the same color. This should make anything in the cells invisible.

b) Protect the worksheet with a password with all of the cells locked.

C) Write VBA code that will ask look to see if the sheet is currently visible or invisible. If the sheet is invible, then ask the user for a password. If the correct password is entered, unprotect the sheet, change the font color to something that will make it visible and then reprotect the sheet (if you don't want the user changing data on it). If the sheet is currently visible, then unprotect, change the font color to the background color, and reprotect the sheet.

d) Protect the VBA project so the user can't see what is being done.

e) Attach the VBA code to a button somewhere in the workbook if desired.

I can assure you that a determined user can and will crack the password.

[John1889]

Hi,
We are in the process of upgrading Office at work in a fully protected environment. For some reason one of the spreadsheet starts running as soon as it is opened. I tried holding the Shift key down a la Access but this didn't stop the code running. Hitting Ctrl>Break didn't stop it, nor clicking the Reset button etc etc. Therefore, the code will not run correctly as the references are still pointing to older version of Office and we want to reset them to upgraded Office. Any suggestions.
Regards,
John

[Help-Desk]

If you still have the old version that it was created with, try opening it that version (hopefully you still have the old original version - not sure if it will open in the older version once it is saved in the newer one). Then, see if there is anything causing it to run automatically when opened. Just a quick thought.

[BurgoEng]

Hi John,
Macro security is probably set to Low. Set it to Medium you can do it in Tools | Macro | Security, then open the workbook and click Disable Macros in the Security Warning dialog. You can then edit VBA. Please note that usually, references are updated automatically when moving to a newer version.
Regards,
Barry.

[John1889]

Thank you BurgoEng, I will try it as you said.

[IronMan]

Is it possible to save an Excel file in two different places simultaneously? Right now I'm having to do a SAVE and a SAVE AS. As I am using this file to maintain my checkbook and want to save it on floppy as well as on c drive.

[BurgoEng]

Record a macro while you manually save and save as. There after, use this macro to save.

[RareBug]

I have never created a macro in Excel. Could any one please walk me through the steps to start one. Thanks you!

[BurgoEng]

You can start with Tools, Macros, Record New Macro. Then do manually the procedure that you want to be automated. Turn the recording off. Then you can review and edit the macro by selecting Tools, Macros, Macros. Select the macro that you just recorded and select edit. Use the help within the visual Basic editor.