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

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
442
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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

Active Member
Joined
Nov 10, 2012
Messages
442
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

Active Member
Joined
Nov 10, 2012
Messages
442

ADVERTISEMENT

Hi

any advise would be really hepful
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
How about writing the info to a very hidden sheet on the existing workbook
 

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
442

ADVERTISEMENT

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

Well-known Member
Joined
Feb 3, 2010
Messages
9,684
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
Sub M1()
MsgBox Environ("Username")
MsgBox Applciation.UserName
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Easiest option is to give your manager a macro to make the sheet visible
Code:
Sub ShowSht()
    Sheet1.Visible = xlSheetVisible
End Sub
& then makes it very hidden on close.
Code:
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

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,363
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top