• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Give permission to users to change data in Excel using VBA code [VBA]

Status
Not open for further replies.
M

miguelc

New Member
Hi there!

I am working on a excel sheet (excel 2010) and, being the sheet locked, I want a specific cell to be given edit permissions to specific users depending on the selection of a drop down list (that is unlocked for everyone). For example, you choose the person called 'Marie' from the dropdown list in cell A2 and automatically range B2, which is locked, is given edit permissions to user 'Marie', allowing her to change values.

Thanks in advance.

Regards,
Miguel
 
The sheet is to remain locked for everyone but for that specific cell (which is accessible to a specific user upon selecting his name from the dropdown list).
Users in general have no access to any other cell on this sheet besides the dropdown one (A2) and this other "variable" cell (B2) depending on the user rights to change values.
 
Why select 'user' from drop down?
If 'miguelc' can select 'Marie' then who is user?
Do every user use same profile then use that file?
If not then You could use Environ("username") to know
who is user and after that unlock wanted cell(s)...
 
I believe I wasn't too clear.. What I meant is that it consists of a shared file, which everyone has access to. The sheet is locked so users can't change the values on the cells, except in the 'validator collumn'(with data validation list) and the 'validation collumn' for the specific user that has been chosen on the list (so he can write an 'OK' therefore giving his agreement).

To better explain this I tried to make an example which you can find in attachment. As you can see, collumn A consists of a data validation list where you can select a validator. A permission is set for this range to be read-write for every user (would add the users if put in place) and the rest of the cells on this sheet is locked.

What I intended is that, by choosing f.e. 'Maria' on cell A2, automatically cell F2 becomes editable by the user Maria. In this sense, is there any way to associate a 'user edit range' to a macro?

Thanks in advance for your help!
 

Attachments

  • Test.xlsx
    8.4 KB · Views: 19
Now this is one sample of 'user sensitive file'.
That 'Welcome'-message is for test purpose.
If Your 'UserName' can found in correct place
then You can edit Your F-column cell.
No need to select from 'drop down'.
... and shared files work!
 

Attachments

  • Test_miguelc.xlsb
    17 KB · Views: 30
Thanks you for your reply, that could actually work but instead of using a "user sensitive file", can it be a "user sensitive cell"? So in this case F2 is locked to the user Maria and F3 to user Dominic and so on depending on the choice of collumn A and that's why the drop-drown list was created.
 
In this version user can edit only own rows F-column cell.
Term own means really own - not others! (like Dominic cannot edit Julia's cell)
Now:
'Maria' could edit cell F2,
'Dominic' >> F3 ...
hmm 2nd 'Maria' now >> F4
and
'Julia' >> F5
... and as I wrote, this is a sample.
 
That's exactly what I want!! Now, how can we do it? Perhaps you could enlighten me a bit.. btw what's the password of the excel file?
 
Test this way ...
1) Open this file
2) You'll get 'SuperUser rights'
3) Edit 'Correct UserNames' to column A
4) Close file
5) if You (as SuperUser) open file You will get next time permit to edit F-Column cells
6) if someone else (Username match) with A-Column names then user will get permit that rows F-Column...

Always 'just' close file to set 'full protection'.

Password? ... hmmm ...
I used same password as You used :)
or I left it empty ... Okay?

Ideas ... Questions?
..This is still like a sample
 

Attachments

  • Test_miguelc.xlsb
    18 KB · Views: 26
Thanks a lot!!
I just tested the file with another user. Here's what happened:
His user is: 937334
Mine is: b05850
The code works while recognizing my user but not 937334.
If I try to change cell H1 to anything else while leaving cell A2 with "b05850", when I reopen the file I can only edit cell F2. But if I try "937334" on cell A2, when opening the file in the other user's session the msgbox says: 937334
and then 'unknown user'.
The same happens if I try to change F1 to 937334 and save the file. User 937334 does not get SuperUser Rights.
I'm pretty sure there is no misspell of his username since the 1st msgbox also gives that value.
Any ideia why this might be happening?
 
On a second note, I just tried with a different User "a23916" and it all works.
It's because it is alphanumeric too, right?
 
Hmm ... I tested with only numbers too and it worked.
Normally ... number would understand as number but some case it will understand as text.
You can test to change A-columns format to text.
... if still no work then I'll figure something else.
... and it is very sensitive ( no extra marks before of after UserName! )
 
Hi there!
Just a final request regarding the protected sheet.
I noticed that anyone can unprotect the sheet in the excel file as it doesn't need any password for that. Therefore, I'm trying to add a password (ActiveSheet.Protect Password="") but if I do so, then a non-superuser that opens the file without knowing the password can no longer change the F column cells refering to his username.
To sum up:
- Is it possible to enable few users to change data in F collumn even if there's a password as mentioned above?
- In case it doesn't work, what if I disable this feature (unprotect sheet) so that nobody can unprotect sheet?
Thanks!!
 
Protection is necessary even in this case.
As I wrote ealier ..This is still like a sample
This should get work someway before can make more settings.
All needed settings depends of Your real file!
Next version:
Now, You're 'SuperUser' and even You don't need to know/fill password.
You should edit those 'UserName' and
maybe ... those UserName no need to be visible.
'Normal' users can see 'yellow cell' which they can edit.
 

Attachments

  • Test_miguelc.xlsb
    21 KB · Views: 38
Hello Vletm, this xls looks like a good solution to poll people with specific questions (varying per person) without complicated mail-arrangements.
Can you post the password, so I can adjust the xls to my requirements?
Thnx.
 
JWTANT
a) Did You read #16 reply? Now, You're 'SuperUser' and even You don't need to know/fill password. It also should mean that...
b) This seems to be from ... about three years ago ... do You remember Yourself - was it ... sunny or rainy day?
c) Any password won't be any password, if everybody would know it!
d) It's not any xls!
 
Hi Vletm,
No I don't remember things that happened so long ago.
I was just hoping to find a solution for a problem.
As I cannot open the file, this is not the case.
Too bad.
Thnx anyway
 
JWTANT
What is a problem?
... is that something to eat?
Did You try to open that file?
I should be possible to open.
I can adjust the xls to my requirements
... could You adjust Your requirements with that file?
 
Yes I can open the file.
But since everything is locked and no password given, I assume that it's of no use to anyone else than miguelc, unless I missed something.
 
Not only was I able to open it and unlock it, despite what @vietm said, using a code that is easily available on Microsoft Docs site called "Unprotected" but I could also read the username and password used in the file. Now this can be dangerous, so I would advise coding the username and password into the thisworkbook module AND having a hidden worksheet used as a database for the passwords.

Here is the proof that I successfully unlocked it - notice the headers and footers in the code.
 
vbacoder1962
There were a good reason why that file was protected as it was.
It was for miguelc use just with those settings.
It would be challenge to use for something else.
I wrote, that I didn't remember some words about three years ago.
Do You remember Yourself - was it ... sunny or rainy day?
I also know that ANY protection can break, if someone wants to do so.
As well as any car, door, house ... whatever.
... and those datas could read from that file without breaking it!
 
vbacoder1962 said:
Not only was I able to open it and unlock it, despite what @vietm said, using a code that is easily available on Microsoft Docs site called "Unprotected" but I could also read the username and password used in the file. Now this can be dangerous, so I would advise coding the username and password into the thisworkbook module AND having a hidden worksheet used as a database for the passwords.

Here is the proof that I successfully unlocked it - notice the headers and footers in the code.
Click to expand...
Hi vbacoder1962
Can you share either the unlocked file or the link to the "unprotected" site, so that I can have a look at the vba-code?
I'm planning to publish (inside my company) a long list of (a lot of) users and their usage of their transactions in our ERP-system, with the request to tick all the transactions that they will keep on using in the future. If I can publish this list as a shared xls-list where anyone can only edit his own data, this would reduce the error-rate significantly. I'm not looking for a hacker-proof solution. With th VBA I can auto-generate this list, instead of editing all cells by hand.
Thnx in advance for any help.
 
Status
Not open for further replies.
Back
Top