Multi User accessible security login an shared Excel Sheet

[vkmummer]

Hi,

I need to maintain an assumption log where multiple users will add their inputs to it regularly. If there is a possibility to provide username/password for each user (refer attached excel) and cells are locked once any data entered in it, it would be easy to avoid any blamings later. i have attached excel sheet with requirements. If anyone can help me on this will be great.

[Keebellah]

Hi, first of all the file shall have to be macro embedded and not an xlsx file as you have uploaded.
Let me see if I understand you correctly:
It refers to the worksheet Assumption log
A user that has access to this file may write something in column C only if column C is empty and therefore unlocked
when a user completes the entry the cell in column D of that row is completed with the logged in user name, column E the time stamp (date and time) and F will be locked too but only a user with Admin rights may edit something there.
Concluding: the ONLY CELL A USER HAS ACCESS TO IS A CELL IN COLUMN C, correct?

Does this mean that the remaining data that is present is already pre-filled?

At what moment does the user have to enter his/her password?
If the user notices he/she has made a mistake that he/she wishes to correct but the cell is already locked, what steps is he/she allowed? May the correction be made?
I understand the Dash Board sheet is hidden and not accessible to users so that they cannot read each other's passwords. Do you, the Admin, create these passwords or does the user do this the first time?
You could even think of a password encryption key so it's unreadable , but that is another story )
Well, do you yourself already think of a way you would approach this?
I do have some ideas but need to know the things I asked before I start.
More important, do you have enough VBA knowledge to maintain this?

[vkmummer]

@keebella,

You have correctly understood my points. Please refer below to clarify your concerns

Column A, B, G & H shall be always accessible/editable to all users.

Column C & F shall be locked once the data entered.

Does this mean that the remaining data that is present is already pre-filled? -- > Data shown in the excel sheet is a sample. Users will start entering the data continuously whenever required.

At what moment does the user have to enter his/her password? -when a user need to enter any assumptions or constraints on a job, he need to log into the sheet and to type the data & save it. may be once he closed the sheet the next user will open and enter the data.

If the user notices he/she has made a mistake that he/she wishes to correct but the cell is already locked, what steps is he/she allowed? May the correction be made? - If a user feels he need to modify the data. he need to approach admin. so that admin can open and edit the data. The main concern behind this is for ex:- during the initiation of a project/job, design team may be discuss something with production team verbally and they will find some solution. later on if the discussed things became an issue, they will start blaming " I had told you the things earlier and you only agreed to proceed this way. now see the thing is happened as i guessed at that time". it may be right/wrong who knows. If any such concern they entered in this excel sheet we will come to know if it is true or false no one will escape

I understand the Dash Board sheet is hidden and not accessible to users so that they cannot read each other's passwords. Do you, the Admin, create these passwords or does the user do this the first time? - Exactly. admin (so far me) will create the username and password and will passed to users and if required the password can be modified.

You could even think of a password encryption key so it's unreadable , but that is another story ) - I don't have no idea/knowledge about what you are pointing to. The main reason i introduced username & password is for the auto update the column D with the user name who is entering the data. if you feel for my requirement such username and password is not required and there is another way that also fine for me.

Well, do you yourself already think of a way you would approach this? - I hope you got the answer for this from above sentences.

I do have some ideas but need to know the things I asked before I start. - Based on my requirement, if there is any feasible way please suggest.

More important, do you have enough VBA knowledge to maintain this? - Absolutely not. But if you provide me the excel with above requirement, I don't think i may need to change something in macro.

[Keebellah]

Does this mean that the remaining data that is present is already pre-filled? -- > Data shown in the excel sheet is a sample. Users will start entering the data continuously whenever required.

Does this mean that new rows can be added by the same users on-the-fly?

I will see what I can do for you, it's not simple, if you don't have VBA knowledge it has no sense that I explain to you how to do it but would have to write something.

[vkmummer]

New rows can be added to all users for entering further data while required columns shall be in locked condition.

If you are telling to do something in VBA code, i can do a little bit.

[Keebellah]

Let me put something together first; I'll try it during the day and let you know as soon as I have made something

[vkmummer]

can anybody help on this

[Keebellah]

Sorry for not getting back to you yet.
I've been quite busy and couldn't really find the time to 'attack' this, and, it's not that simple.
Hope to make some time free for it asap

[vkmummer]

@keebellah or any body - can you please help on this

[Keebellah]

No promises, but I'll see if I can do something for you.

[vkmummer]

Hi,

can anybody help with this, please?

[Scenic3]

Hi,

can anybody help with this, please?

Hi vkmummer

I hope this can help you so far.

Best regards
Scenic3

[vkmummer]

Hi,

it shows error while clicking any cell/login

[Keebellah]

Yeah, typical error if not taking into account 64 32 bit

Replace the top section of the PasswordMasked vba with the code below

Please Login or Register  to view this content.

[vkmummer]

Dear,

Still it shows error while i click login. Should i do anything before click in login.

Anyway many thanks for taking such efforts for helping me.

[rorya]

The 64bit declarations should be:

Please Login or Register  to view this content.

and the relevant variables will need to be adjusted to be conditionally declared LongPtr rather than Long.

[vkmummer]

Hi,

can you please update the code in excel sheet itself.

[Keebellah]

Is it the same error?
Did you paste the code correctly (our use the file I attached (which has the corrected code)

[Keebellah]

I noticed the the LongPtr or not did not make the difference with other files I wrote for others; but ... it doesn't hurt

[vkmummer]

Hi,

I used the same updated file provided by you. but shows the same error.

[Keebellah]

Compile Errro type mismatch is something entirely different that the first error
You'll have to take a look at the code and the PrtLong Rory suggested.
Is your Office version 64-bit?

[Keebellah]

Well, changed it as Rory suggested.
Give it a try and report EXACTLY where and which error.
Type Mismatch is something else

[vkmummer]

Hi,

Am using 64 bit version. but still the same issue.

I opened and clicked the login button then 1 window poped up as shown below. when i clicked Ok it shows a yellow highlighted macro texts.

How i can use admin rights (all cells editable) also. the black vertical texts need to be removed. this was shown to make you understand my requirements. Alos date format need to be dd-mmm-yy (01-Jun-20).

[rorya]

That was not the code I suggested. The amended workbook is attached. You could still get a Type Mismatch error if the username entered does not exist, which was a problem in your original code too.

[Scenic3]

your login code should be "admin" and your password "Password"

I'm sorry bud I did make this on the 32-bit version.

Besr regards,
Scenic3

[vkmummer]

Hi Rorya & Scenic3,

it is working fine. but even in the admin i am not unable to remove the vertical indication texts with "-->" which was provided in the initial excel sheet for understanding my requirement. can you please delete that and revert back.

Also i need below

- Assumption made by columns shall be updated based on the user enetered
-Date format need to be dd-mmm-yy (01-Jun-20)
-status & action columns shall be accessible for "restricted users as well"

[Scenic3]

Hi Rorya & Scenic3,

it is working fine. but even in the admin i am not unable to remove the vertical indication texts with "-->" which was provided in the initial excel sheet for understanding my requirement. can you please delete that and revert back.

Also i need below

- Assumption made by columns shall be updated based on the user enetered
-Date format need to be dd-mmm-yy (01-Jun-20)
-status & action columns shall be accessible for "restricted users as well"

Maybe this is better.

[vkmummer]

Dear scenic,

Thank you very much. still there is below issue.

- even if i logged with other users credential, "assumption made by" coulmn is updating with my excel name (name registered in my PC for office 365). I need to change this into the user name who logged in
- also the date format if possible -dd-mmm-yy

[Keebellah]

One thnig is the logged on user, not the one using the file.
So if you are working on your own system and looged in there THAT's the username, not another one!!!

[Scenic3]

I suppose the user is logged in on his own system, so his own username.

I have changed the format of the date to 01-June-2020

Best regards
Scenic3