VBA - Protect Sheet by super user or simple user

[ozstrik3r69]

Dears good morning

Coming today asking for a help for this new project that I´ve done.
It is a simple user form, where a basic analyst user will have to fill the boxes before confirming and start a new "log".
This user form, once the boxes filled, write all the information on a sheet, that I want to keep "very Hidden" (sheet 1)

What I want for this project, :

To be sure that the "simple" analyst will not be able to change manually any kind of information on the sheet 1, once he filled the form.
What I tried was to protect the sheet by password but i faced a problem :
Once the sheet 1 was protected, the user form cannot write on the sheet 1, it means that the sheet 1 needs to be unprotected to give the possibility to the analyst to fill the form.

What I am looking for :
I was wondering if there is a way to give access to the sheet 1, restricting by user the rights.
For example :
If the analyst with logon "BBKing", can be consider has an analyst and can ONLY "write" on the sheet 1.
When I say "Write", I mean filling the form and never face any kind of problem ...

If the analyst with logon "NMandela", can be consider has a super user and can write, delete, add row and so on on the sheet 1.

I do not know if my explanation are good enough, but in case need I am ready to explain more and more to get this project finish.

Waiting for any feedback
My very best regards

Ozstrik3r69

P.S : My code, for sure, is not clean enough, is not the best one... but I did my best to get that user form working...
Please feel free to clean, change, update the code as much as you want.

[gmr4evr1]

For a simple user you can unprotect the sheet at the beginning of the code, then protect it again at the end so that the user cannot edit sheet 1.
For a super user, you can have an open workbook code that if that user is logged on then unprotect sheet1.

[CK76]

Excel's protection of data inside workbook... is at best, nothing more than hindrance.
Anyone, with rudimentary understanding of VBA can and likely will bypass protection.

Although there are 3rd party add-in that will increase security...
Before you go down this path much further... I suggest you invest in proper database (MS Access, MS SQL, MySQL etc).

[ozstrik3r69]

Excel's protection of data inside workbook... is at best, nothing more than hindrance.
Anyone, with rudimentary understanding of VBA can and likely will bypass protection.

Although there are 3rd party add-in that will increase security...
Before you go down this path much further... I suggest you invest in proper database (MS Access, MS SQL, MySQL etc).

Unfortunately I know that with VBA you can unprotect whatever you want in excel. I know that Password, no matter if it is on the workbook or even on the VBA property , are useless. But for the time being, excel is the only solution that I have.

For a simple user you can unprotect the sheet at the beginning of the code, then protect it again at the end so that the user cannot edit sheet 1.
For a super user, you can have an open workbook code that if that user is logged on then unprotect sheet1.

What I was wondering is, how to get excel understand that one user is a simple user and the other user is a super user.
If I can do that way it will be better for me. Depends of the logon, my code will understand the way to unprotect the sheet.


In advance thank you for your help folks.
Waiting for feedback
Regards

[CK76]

What I was wondering is, how to get excel understand that one user is a simple user and the other user is a super user.

You can use Environ function to get user name and check against array. Or some other method (Ex. Use InStr function on concatenated user name list etc).

Please Login or Register  to view this content.

It's also a good idea to ensure the sheet is protected before workbook is closed/saved.

[ozstrik3r69]

Please Login or Register  to view this content.

Is that code can separate who is super user and simple user ?
If I inform BBKing, Nmandela and so on, is the code will understand that BBking can only filled the form and write on the sheet 1. if it is Nmandela, is the code will understand that Nmandela can do whatever he wants on the sheet 1 ?

Sorry for that questions, hope i do not bother you

Waiting for your feedback
Regards

[CK76]

Basically, when workbook is opened by users specified, it will unprotect the sheet you specified. So they can do what they want with it.

Otherwise it does not unprotect the sheet.
So, you'd use userform to unprotect just before data is submitted and protect immediately after. So that user don't directly enter info to the sheet.

Edit: Oh wait, I see that you have OSX... If I recall, Environ is windows function... Then you'd just use Inputbox or some other method and have the user verify their credential/password to unlock the sheet.

[ozstrik3r69]

Basically, when workbook is opened by users specified, it will unprotect the sheet you specified. So they can do what they want with it.

Otherwise it does not unprotect the sheet.
So, you'd use userform to unprotect just before data is submitted and protect immediately after. So that user don't directly enter info to the sheet.

Edit: Oh wait, I see that you have OSX... If I recall, Environ is windows function... Then you'd just use Inputbox or some other method and have the user verify their credential/password to unlock the sheet.

CK76,
First thank you for your help. it is always important to say "thank you" for this kind of help. You are spending your time on this post and for that I am grateful.

Second, I will try your code on my sheet, but what I tried during that time, has been to unprotect the sheet, to insert the line that I need and then protect the sheet.
Leaving only the first row unprotected.
It is a dumb turnaround but it works..for the moment.

Please Login or Register  to view this content.

I keep continue finding a better thing and for that reason I will try your code.

Once again thank you for your help.
Regards