excel 2016: how to allow different users to edit different range of cells
How to allow different users (John, Paul and Mika) to edit different cells in worksheet ?
Johnis to edit only cellsA2toC5(RANGE 1)
Mikais to edit only cellsE2toG5(RANGE 2)
Paulis to edit only cellsH2toK5(RANGE 3)
In 'allow users to edit ranges', I created 3 ranges for above cells with no password. Each range contains the user name permission.
When I select range 1, then the allow users to edit ranges option is disabled.
I'm not able to do apply same for other 2 users...
Can someone please help and provide the right steps / vba code?
excel excel-2016
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
asked Nov 21 '18 at 15:49
netnet
447
add a comment |
How to allow different users (John, Paul and Mika) to edit different cells in worksheet ?
Johnis to edit only cellsA2toC5(RANGE 1)
Mikais to edit only cellsE2toG5(RANGE 2)
Paulis to edit only cellsH2toK5(RANGE 3)
In 'allow users to edit ranges', I created 3 ranges for above cells with no password. Each range contains the user name permission.
When I select range 1, then the allow users to edit ranges option is disabled.
I'm not able to do apply same for other 2 users...
Can someone please help and provide the right steps / vba code?
excel excel-2016
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
asked Nov 21 '18 at 15:49
netnet
447
I am sorry to tell you but Excel offers little to no security once you allow someone to open an Excel file for editing. Of course, you could code something with VBA and capture all change events on all relevant sheets and afterwards set a password on the VBA code. But this will only work if the user enables VBA. If you want really security then get a database server as a back-end (possibly MSSQL). If that's not an option then I'd make several different Excel files and assign permissions on the files. Other than that you can try several things but will never get any real security; just attempts
– Ralph
Nov 21 '18 at 16:25
can we force cells locked for a specific users??
– net
Nov 22 '18 at 11:20
add a comment |
How to allow different users (John, Paul and Mika) to edit different cells in worksheet ?
Johnis to edit only cellsA2toC5(RANGE 1)
Mikais to edit only cellsE2toG5(RANGE 2)
Paulis to edit only cellsH2toK5(RANGE 3)
In 'allow users to edit ranges', I created 3 ranges for above cells with no password. Each range contains the user name permission.
When I select range 1, then the allow users to edit ranges option is disabled.
I'm not able to do apply same for other 2 users...
Can someone please help and provide the right steps / vba code?
excel excel-2016
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
asked Nov 21 '18 at 15:49
netnet
447
How to allow different users (John, Paul and Mika) to edit different cells in worksheet ?
Johnis to edit only cellsA2toC5(RANGE 1)
Mikais to edit only cellsE2toG5(RANGE 2)
Paulis to edit only cellsH2toK5(RANGE 3)
In 'allow users to edit ranges', I created 3 ranges for above cells with no password. Each range contains the user name permission.
When I select range 1, then the allow users to edit ranges option is disabled.
I'm not able to do apply same for other 2 users...
Can someone please help and provide the right steps / vba code?
excel excel-2016
excel excel-2016
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
asked Nov 21 '18 at 15:49
netnet
447
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
asked Nov 21 '18 at 15:49
netnet
447
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
edited Nov 22 '18 at 19:30
Sk83r1l4m4
11711
11711
asked Nov 21 '18 at 15:49
netnet
447
asked Nov 21 '18 at 15:49
netnet
447
asked Nov 21 '18 at 15:49
netnet
447
447
I am sorry to tell you but Excel offers little to no security once you allow someone to open an Excel file for editing. Of course, you could code something with VBA and capture all change events on all relevant sheets and afterwards set a password on the VBA code. But this will only work if the user enables VBA. If you want really security then get a database server as a back-end (possibly MSSQL). If that's not an option then I'd make several different Excel files and assign permissions on the files. Other than that you can try several things but will never get any real security; just attempts
– Ralph
Nov 21 '18 at 16:25
can we force cells locked for a specific users??
– net
Nov 22 '18 at 11:20
add a comment |
I am sorry to tell you but Excel offers little to no security once you allow someone to open an Excel file for editing. Of course, you could code something with VBA and capture all change events on all relevant sheets and afterwards set a password on the VBA code. But this will only work if the user enables VBA. If you want really security then get a database server as a back-end (possibly MSSQL). If that's not an option then I'd make several different Excel files and assign permissions on the files. Other than that you can try several things but will never get any real security; just attempts
– Ralph
Nov 21 '18 at 16:25
can we force cells locked for a specific users??
– net
Nov 22 '18 at 11:20
I am sorry to tell you but Excel offers little to no security once you allow someone to open an Excel file for editing. Of course, you could code something with VBA and capture all change events on all relevant sheets and afterwards set a password on the VBA code. But this will only work if the user enables VBA. If you want really security then get a database server as a back-end (possibly MSSQL). If that's not an option then I'd make several different Excel files and assign permissions on the files. Other than that you can try several things but will never get any real security; just attempts
– Ralph
Nov 21 '18 at 16:25
I am sorry to tell you but Excel offers little to no security once you allow someone to open an Excel file for editing. Of course, you could code something with VBA and capture all change events on all relevant sheets and afterwards set a password on the VBA code. But this will only work if the user enables VBA. If you want really security then get a database server as a back-end (possibly MSSQL). If that's not an option then I'd make several different Excel files and assign permissions on the files. Other than that you can try several things but will never get any real security; just attempts
– Ralph
Nov 21 '18 at 16:25
can we force cells locked for a specific users??
– net
Nov 22 '18 at 11:20
can we force cells locked for a specific users??
– net
Nov 22 '18 at 11:20
add a comment |
1 Answer
1
active
oldest
votes
In the workbook_beforeclose event we lock all cells. In the workbook.open event we obtain the user's logon name and selectively unlock the desired range for that logon.
If the user doesn't enable macros then everything is protected, if they do enable then only the desired range is available. (You must close the spreadsheet yourself once for the protection to take effect). This assumes a corporate environment in which logging on is enforced. This code goes into the workbook module:
Option Explicit
Const pword = "your password here"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect pword
ws.UsedRange.Locked = True
ws.Protect pword
Next ws
End Sub
Private Sub Workbook_Open()
Dim LogonName As String
LogonName = Environ("UserName")
Dim rangetoedit As Range
Select Case LogonName
Case "John"
Set rangetoedit = Worksheets(1).Range("A2:C5")
Case "Paul"
Set rangetoedit = Worksheets(1).Range("H2:K5")
Case "Mika"
Set rangetoedit = Worksheets(1).Range("E2:G5")
End Select
Worksheets(1).Unprotect pword
rangetoedit.Locked = False
Worksheets(1).Protect pword
End Sub
You will want to password protect your vba code as well
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415762%2fexcel-2016-how-to-allow-different-users-to-edit-different-range-of-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
In the workbook_beforeclose event we lock all cells. In the workbook.open event we obtain the user's logon name and selectively unlock the desired range for that logon.
If the user doesn't enable macros then everything is protected, if they do enable then only the desired range is available. (You must close the spreadsheet yourself once for the protection to take effect). This assumes a corporate environment in which logging on is enforced. This code goes into the workbook module:
Option Explicit
Const pword = "your password here"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect pword
ws.UsedRange.Locked = True
ws.Protect pword
Next ws
End Sub
Private Sub Workbook_Open()
Dim LogonName As String
LogonName = Environ("UserName")
Dim rangetoedit As Range
Select Case LogonName
Case "John"
Set rangetoedit = Worksheets(1).Range("A2:C5")
Case "Paul"
Set rangetoedit = Worksheets(1).Range("H2:K5")
Case "Mika"
Set rangetoedit = Worksheets(1).Range("E2:G5")
End Select
Worksheets(1).Unprotect pword
rangetoedit.Locked = False
Worksheets(1).Protect pword
End Sub
You will want to password protect your vba code as well
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
add a comment |
In the workbook_beforeclose event we lock all cells. In the workbook.open event we obtain the user's logon name and selectively unlock the desired range for that logon.
If the user doesn't enable macros then everything is protected, if they do enable then only the desired range is available. (You must close the spreadsheet yourself once for the protection to take effect). This assumes a corporate environment in which logging on is enforced. This code goes into the workbook module:
Option Explicit
Const pword = "your password here"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect pword
ws.UsedRange.Locked = True
ws.Protect pword
Next ws
End Sub
Private Sub Workbook_Open()
Dim LogonName As String
LogonName = Environ("UserName")
Dim rangetoedit As Range
Select Case LogonName
Case "John"
Set rangetoedit = Worksheets(1).Range("A2:C5")
Case "Paul"
Set rangetoedit = Worksheets(1).Range("H2:K5")
Case "Mika"
Set rangetoedit = Worksheets(1).Range("E2:G5")
End Select
Worksheets(1).Unprotect pword
rangetoedit.Locked = False
Worksheets(1).Protect pword
End Sub
You will want to password protect your vba code as well
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
add a comment |
In the workbook_beforeclose event we lock all cells. In the workbook.open event we obtain the user's logon name and selectively unlock the desired range for that logon.
If the user doesn't enable macros then everything is protected, if they do enable then only the desired range is available. (You must close the spreadsheet yourself once for the protection to take effect). This assumes a corporate environment in which logging on is enforced. This code goes into the workbook module:
Option Explicit
Const pword = "your password here"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect pword
ws.UsedRange.Locked = True
ws.Protect pword
Next ws
End Sub
Private Sub Workbook_Open()
Dim LogonName As String
LogonName = Environ("UserName")
Dim rangetoedit As Range
Select Case LogonName
Case "John"
Set rangetoedit = Worksheets(1).Range("A2:C5")
Case "Paul"
Set rangetoedit = Worksheets(1).Range("H2:K5")
Case "Mika"
Set rangetoedit = Worksheets(1).Range("E2:G5")
End Select
Worksheets(1).Unprotect pword
rangetoedit.Locked = False
Worksheets(1).Protect pword
End Sub
You will want to password protect your vba code as well
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
In the workbook_beforeclose event we lock all cells. In the workbook.open event we obtain the user's logon name and selectively unlock the desired range for that logon.
If the user doesn't enable macros then everything is protected, if they do enable then only the desired range is available. (You must close the spreadsheet yourself once for the protection to take effect). This assumes a corporate environment in which logging on is enforced. This code goes into the workbook module:
Option Explicit
Const pword = "your password here"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect pword
ws.UsedRange.Locked = True
ws.Protect pword
Next ws
End Sub
Private Sub Workbook_Open()
Dim LogonName As String
LogonName = Environ("UserName")
Dim rangetoedit As Range
Select Case LogonName
Case "John"
Set rangetoedit = Worksheets(1).Range("A2:C5")
Case "Paul"
Set rangetoedit = Worksheets(1).Range("H2:K5")
Case "Mika"
Set rangetoedit = Worksheets(1).Range("E2:G5")
End Select
Worksheets(1).Unprotect pword
rangetoedit.Locked = False
Worksheets(1).Protect pword
End Sub
You will want to password protect your vba code as well
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
answered Nov 22 '18 at 15:10
Harassed DadHarassed Dad
3,2291612
3,2291612
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
add a comment |
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Have you ever downloaded LibreOffice and opened your "secure" file with any other non-Excel application? This software does not much care about your VBA password (just like many other non-Excel applications which still allow editing Excel files). Also, what happens if the file gets force-closed (because Excel crashes or there is an outage)? What happens if I do not enable VBA? I don't want to be overly critical. I am just saying there is pretty sizable hole in "security" as offered above.
– Ralph
Nov 22 '18 at 15:42
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
Usually in a corporate environment "security" is designed to prevent errors and accidents, not defend against hackers. And no one can download anything in such an environment, so open office is not an issue. And malicious activities would result in dismissal. You're right about force-crash though - it would be better to have the open routine repeat the close routine first before unlocking the correct region
– Harassed Dad
Nov 23 '18 at 9:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53415762%2fexcel-2016-how-to-allow-different-users-to-edit-different-range-of-cells%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I am sorry to tell you but Excel offers little to no security once you allow someone to open an Excel file for editing. Of course, you could code something with VBA and capture all change events on all relevant sheets and afterwards set a password on the VBA code. But this will only work if the user enables VBA. If you want really security then get a database server as a back-end (possibly MSSQL). If that's not an option then I'd make several different Excel files and assign permissions on the files. Other than that you can try several things but will never get any real security; just attempts
– Ralph
Nov 21 '18 at 16:25
can we force cells locked for a specific users??
– net
Nov 22 '18 at 11:20