Workbook that gives relevant read/write acces depend on user

[FloggingDolphin]

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

 

[mightymishra]

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]

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

 

[mightymishra]

do the departments enter respective data on different sheets.

 

[FloggingDolphin]

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).