Hello,
I am hoping to get some insight to an issue I ran into recently. I am not an expert by any means and this was my first macro I created. I posed this question onto another forum and there was no response, I heard about this forum and hoping to get some ideas. Here is the summary:
I am using Excel version 2303 (Build 16227.20318) and created a financial forecasting spreadsheet with several different tabs. I wanted to be able to have multiple users access this same document, but based on login credentials, have access to only sheets relevant to them since there is confidential information on some of the tabs.
I created an admin tab that has User Name, Password and gives full access, read-only access, or no access to each of the sheets based on security profile of each user. Then, I built a login form with a macro that runs access upon login with credentials. I tested with multiple people and had each person try to break into various tabs and found no issues. After placing it on the Sharepoint site, unfortunately, the security login failed and I am not sure why. Here is what happened:
User 1: who had full access to all tabs was in the sheet
User 2: who only had access to 5 sheets was presented the login form and used his credentials, but when the file opened, it opened to the same access that User 1 had opened. User 1 was in the file at the time as well. They were using SharePoint and opened Excel in APP when this happened.
Does anyone have any ideas on why this may have happened? I was thinking it could be an issue with the macro to maybe not allow another user to enter the document if there is another user in it, but then I was thinking because this was place onto a Sharepoint site, a collaboration tool, that perhaps it doesn't matter what security you build in because Sharepoint's existence is to allow collaboration onto a sheet. I thought there might be two ways to attempt to solve: 1) update the macro to "fail login" if another user is in the sheet (which I am not 100% sure how to do or if this will work on Sharepoint) OR 2) Save it to a different platform that is not designed as a collaboration tool.
Thoughts?
I am hoping to get some insight to an issue I ran into recently. I am not an expert by any means and this was my first macro I created. I posed this question onto another forum and there was no response, I heard about this forum and hoping to get some ideas. Here is the summary:
I am using Excel version 2303 (Build 16227.20318) and created a financial forecasting spreadsheet with several different tabs. I wanted to be able to have multiple users access this same document, but based on login credentials, have access to only sheets relevant to them since there is confidential information on some of the tabs.
I created an admin tab that has User Name, Password and gives full access, read-only access, or no access to each of the sheets based on security profile of each user. Then, I built a login form with a macro that runs access upon login with credentials. I tested with multiple people and had each person try to break into various tabs and found no issues. After placing it on the Sharepoint site, unfortunately, the security login failed and I am not sure why. Here is what happened:
User 1: who had full access to all tabs was in the sheet
User 2: who only had access to 5 sheets was presented the login form and used his credentials, but when the file opened, it opened to the same access that User 1 had opened. User 1 was in the file at the time as well. They were using SharePoint and opened Excel in APP when this happened.
Does anyone have any ideas on why this may have happened? I was thinking it could be an issue with the macro to maybe not allow another user to enter the document if there is another user in it, but then I was thinking because this was place onto a Sharepoint site, a collaboration tool, that perhaps it doesn't matter what security you build in because Sharepoint's existence is to allow collaboration onto a sheet. I thought there might be two ways to attempt to solve: 1) update the macro to "fail login" if another user is in the sheet (which I am not 100% sure how to do or if this will work on Sharepoint) OR 2) Save it to a different platform that is not designed as a collaboration tool.
Thoughts?
