Don't know if this possible and what advice can you give

[Uzma Shaheen]

Hi All

i have this spreadsheet where I have stored on a shared drive

this spreadsheet has a form (macro) that everyone can use and run however when the code is run, it inputs some data like who ran the form etc and the address info input on the form in a spreadsheet that is only on my desktop so no one has access to this spreadsheet.

now the problem is when I run the code it's fine as the other spreadsheet is on my desktop but if someone else runs it, then it will error because it's trying to input on the spreadsheet on my desktop.

i can't have this second spreadsheet on a shared drive as no1 is meant to know about this

how can I get around this and what do you advise is the best way to do it

thank you

 

[skywriter]

I would think you could change the code and come up with a fix. Perhaps the spreadsheet on your desktop needs to be on a shared drive. I would think that with the proper configuration the code could write to the spreadsheet, but the users would not have any permissions to that spreadsheet. Maybe the code can be rewritten to work with the spreadsheet staying on your computer.

 

[Uzma Shaheen]

The spreadsheet is protected but no1 knows about this and every other folder in a shared drive people will have access to so they will know this spreadsheet exists if I was to put on shared drive

any code ideas or anything thank you

 

[Uzma Shaheen]

Bump

 

[Uzma Shaheen]

Hi

any advise would be really hepful

 

[Fluff]

How about writing the info to a very hidden sheet on the existing workbook

 

[Uzma Shaheen]

Aaaaa that could work. I guess I'll have to do that via code and unhide and hide once the data is input.

problem is I'm writing this code for my manager and he won't really know how to go to vba to unhide when he wants to view the info on this very hidden sheet

how would you go about this?

I'm thinking maybe if the username is the manager then I can keep this very hidden sheet visible at all times but if someone else opens it then it stays very hidden at all times.

what code can I have to achieve this and is the best way to go about it?

 

[JackDanIce]

I had a similar problem before where one person needed access to the very hidden sheet, but not via VBA code edits. You can create a sheet where your boss has to enter a password and if it's correct, unhide the very hidden sheet.

Also, try this:

Sub M1()
MsgBox Environ("Username")
MsgBox Applciation.UserName
End Sub

 

[Fluff]

Easiest option is to give your manager a macro to make the sheet visible

Sub ShowSht()
    Sheet1.Visible = xlSheetVisible
End Sub

& then makes it very hidden on close.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheet1.Visible = xlSheetVeryHidden
    Workbooks("[COLOR=#ff0000]ZZ1.xls[/COLOR]").Save
End Sub

changing file name to suit

 

[skywriter]

The spreadsheet is protected but no1 knows about this and every other folder in a shared drive people will have access to so they will know this spreadsheet exists if I was to put on shared drive

any code ideas or anything thank you

I guess things have changed in the last seven years since I was in charge of the network at my office.
Back then I had complete control over drives, folders and files. I could create a folder and assign groups or just individuals permission to that folder. Permissions ranged from read only, write only, to full control to none. Not sure what your issue is with this seems like you are over complicating the issue. If you have a file now on your computer and the reason no one else can use that file is because it's on your computer, I would just put a folder on the network put the file in it and lock every one except my boss and myself out of it. They won't know what's in the folder, if they click on it they are denied even seeing what is in it, problem solved.