Workbook that gives relevant read/write acces depend on user

FloggingDolphin

New Member
Joined
Jul 3, 2005
Messages
25
Hi there,

I’ve been asked for some excel help from my colleague, but unfortunately she hasn’t provided me with her worksheet yet.

Basically there is two departments (say Department A and B) who need to work on the worksheet. My colleague wants the worksheet to work in such a way that Department A has only read-only access to Department B’s data, and vice versa, but both Departments can alter their own data in the worksheet.

I was thinking that one could sort of write a macro to give relevant data access to both departments by giving each one a different password and each password would activate different access settings.

The problem is that this will only work if they run the worksheet with macro’s enabled. So how can I make a worksheet that forces them to enable macros?

The best thing I can come up with is :

Create a macro with the source code having a password.
When you exit the workbook / or save it, the macro coverts the data into scrambled characters and displays a message called “YOU MUST ENABLE MACROS TO USE THIS WORKSHEET” when the workbook is saved or exit. That was the only way to convert the worksheet back into its normal status is to enable macros.

Sorry for lack of worksheet.

I've also posted this question on:

http://www.ozgrid.com/forum/showthread.php?p=218402#post218402

Mark
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

mightymishra

Board Regular
Joined
Oct 18, 2002
Messages
65
i have one more idea, check if this is fine.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Windows("Book1.xls").Visible = False
End Sub

Private Sub Workbook_Open()
Windows("Book1.xls").Visible = True
End Sub

hence the file will always be invisible if not enabled
 

FloggingDolphin

New Member
Joined
Jul 3, 2005
Messages
25
thanks, that's definately a helpful one. I just hope that if they do get it open they don't figure out to add a macro to have it visible before closing (well I guess why would they do that anyway).

I just need to know now how to have a macro that has them enter either of two passwords and depending what password is entered they will get different access rights.

mark
 

FloggingDolphin

New Member
Joined
Jul 3, 2005
Messages
25
Hi there, sorry for the delay.

The departments enter data on the same sheet.
I've created a sample of what the sheet might look like but I've only managed to attach it/post it on the other forum link I mentioned earlier. Hope that helps understand it better.

Basically Team A should have read/write access to the Team A column and Read-only access to Team B Column. Team B is vice-versa (ie. Read/write for Team B column and Read-only for Team A column).
 

Watch MrExcel Video

Forum statistics

Threads
1,119,270
Messages
5,577,114
Members
412,768
Latest member
klig
Top