Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I tie a workbook or worksheet password to a user ID
I have 110 workbooks with individual data for people to use, I've been trying
to use the password box to create passwords that tie it to their user ID so I don't have to make up 110 passwords. Can this be done? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I tie a workbook or worksheet password to a user ID
Just to be clear...you want a central point of access to link users to their
individual 110 workbooks? Or do you need restricted access within each of the workbooks? Personally, I wouldn't require an extra password, I think if you know who it is from the username, you don't really need a password unless you have very sensitive data, or a high risk of someone being on another person's PC and trying to open this file (in our organization we don't have that risk) In any event, try something like LanID = UCase(VBA.Environ("UserName")) to get your username to open an individual's workbook, assuming it is named with their username, you could use something like: 'Check to see if the individual workbook exists Dim fFileExists As Boolean fFileExists = (Len(Dir(MyPath & LanID & ".xls")) 0) If fFileExists = True Then 'Check to see if individual file is already open On Error Resume Next Windows(LanID & ".xls").Activate If Err < 0 Then 'file wasn't found Err = 0 'reset err code Set RnRUserWkbk = Workbooks.Open(MyPath & LanID & ".xls", False, False) Else Workbooks(LanID & ".xls").Activate End If On Error GoTo 0 Else 'No File Found MsgBox "The system was unable to locate an existing workbook for this user" End If Once you have their username, you can use that directly to control access to whatever you want. I have one workbook where I hide and show sheets based on the username of the person opening the workbook (so each person only sees certain sheets). You could use the code snippet aboveto open other workbooks- for example, everyone has a shortcut to a master workbook, which has code to open their individual workbook, then close itself. HTH, Keith "RocketMan" wrote in message ... I have 110 workbooks with individual data for people to use, I've been trying to use the password box to create passwords that tie it to their user ID so I don't have to make up 110 passwords. Can this be done? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I tie a workbook or worksheet password to a user ID
Each individual workbook has hyperlinks to procedures and a counter next to
the hyperlink. The workbooks are in SharePoint. We want to monitor these people and make sure they read procedures. I don't want someone opening another persons workbook. This looks like it will work, I will give it a try. If you have another idea how to do this let me know. People don't know each others user IDs. Thank a lot Keith. "Keith" wrote: Just to be clear...you want a central point of access to link users to their individual 110 workbooks? Or do you need restricted access within each of the workbooks? Personally, I wouldn't require an extra password, I think if you know who it is from the username, you don't really need a password unless you have very sensitive data, or a high risk of someone being on another person's PC and trying to open this file (in our organization we don't have that risk) In any event, try something like LanID = UCase(VBA.Environ("UserName")) to get your username to open an individual's workbook, assuming it is named with their username, you could use something like: 'Check to see if the individual workbook exists Dim fFileExists As Boolean fFileExists = (Len(Dir(MyPath & LanID & ".xls")) 0) If fFileExists = True Then 'Check to see if individual file is already open On Error Resume Next Windows(LanID & ".xls").Activate If Err < 0 Then 'file wasn't found Err = 0 'reset err code Set RnRUserWkbk = Workbooks.Open(MyPath & LanID & ".xls", False, False) Else Workbooks(LanID & ".xls").Activate End If On Error GoTo 0 Else 'No File Found MsgBox "The system was unable to locate an existing workbook for this user" End If Once you have their username, you can use that directly to control access to whatever you want. I have one workbook where I hide and show sheets based on the username of the person opening the workbook (so each person only sees certain sheets). You could use the code snippet aboveto open other workbooks- for example, everyone has a shortcut to a master workbook, which has code to open their individual workbook, then close itself. HTH, Keith "RocketMan" wrote in message ... I have 110 workbooks with individual data for people to use, I've been trying to use the password box to create passwords that tie it to their user ID so I don't have to make up 110 passwords. Can this be done? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I tie a workbook or worksheet password to a user ID
If you are just trying to restrict each user from opening someone else's
workbook, I would do the following: (1) Add a blank worksheet to each workbook (given that your workbooks already exist, I'd automate all of this- at least on our servers, remote accessing workbooks manually takes too long) and on that sheet, add text that says something to the effect of "either you are not the target user of this workbook, or you do not have macros enabled...blah blah blah". Have all of your other sheets veryhidden at this point. (2) In the workbook open event, check the username (LanID code below) and if it matches the name of the workbook (presumes your workbook names are based on LanID, e.g. JGESHAR.xls), automatically unhide the sheet(s) they need to use, and hide the coversheet. In the workbook close event, make all sheets veryhidden except the coversheet, so that the workbook is set for the next time it is opened. That way, if the wrong user opens the workbook, they won't be able to access the links or cause problems, and still allows you to have code in a separate workbook to 'scrape' the data from the hidden sheets when you need it. Of course, there is some risk in equating "clicking on a hyperlink" with actually reading (and understanding) procedures. Also, the code here probably wouldn't be accepted by some regulatory agencies as a 'digital signature' equivalent to hardcopy signing a 'read & understand' training document. One other thought; if you wanted to get really fancy, you could use another hidden sheet to store additional information on the user's behavior- the first thing that comes to mind is each date/time that a link is clicked (presuming that /is/ equivalent to reading a procedure). That would let you know if they are reading it within the timelines specified by their curriculum (e.g. every 6 months or 12 months or whatever), the amount of time between readings, and if certain procedures are read more frequently than required, that would be good data to indicate that a procedure is complicated and might benefit from a process improvement (six sigma, lean, etc.) project. Another thing you could do is use the time difference between date/time stamps to see the minimum amount of time someone had a link open before clicking on the next one. While they could be opening them to print and read later, it would at least alert you to the possibility of folks who might be opening and clicking just to get their "count" (as you described it) up, by clicking and closing each one, as opposed to the user who has a gap of 10, 20, or 30+ minutes between each one, showing that they had each procedure open long enough to have actually read it before clicking the next one. HTH, Keith "RocketMan" wrote in message ... Each individual workbook has hyperlinks to procedures and a counter next to the hyperlink. The workbooks are in SharePoint. We want to monitor these people and make sure they read procedures. I don't want someone opening another persons workbook. This looks like it will work, I will give it a try. If you have another idea how to do this let me know. People don't know each others user IDs. Thank a lot Keith. "Keith" wrote: Just to be clear...you want a central point of access to link users to their individual 110 workbooks? Or do you need restricted access within each of the workbooks? Personally, I wouldn't require an extra password, I think if you know who it is from the username, you don't really need a password unless you have very sensitive data, or a high risk of someone being on another person's PC and trying to open this file (in our organization we don't have that risk) In any event, try something like LanID = UCase(VBA.Environ("UserName")) to get your username to open an individual's workbook, assuming it is named with their username, you could use something like: 'Check to see if the individual workbook exists Dim fFileExists As Boolean fFileExists = (Len(Dir(MyPath & LanID & ".xls")) 0) If fFileExists = True Then 'Check to see if individual file is already open On Error Resume Next Windows(LanID & ".xls").Activate If Err < 0 Then 'file wasn't found Err = 0 'reset err code Set RnRUserWkbk = Workbooks.Open(MyPath & LanID & ".xls", False, False) Else Workbooks(LanID & ".xls").Activate End If On Error GoTo 0 Else 'No File Found MsgBox "The system was unable to locate an existing workbook for this user" End If Once you have their username, you can use that directly to control access to whatever you want. I have one workbook where I hide and show sheets based on the username of the person opening the workbook (so each person only sees certain sheets). You could use the code snippet aboveto open other workbooks- for example, everyone has a shortcut to a master workbook, which has code to open their individual workbook, then close itself. HTH, Keith "RocketMan" wrote in message ... I have 110 workbooks with individual data for people to use, I've been trying to use the password box to create passwords that tie it to their user ID so I don't have to make up 110 passwords. Can this be done? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I tie a workbook or worksheet password to a user ID
Thanks for the reply. I just got to it as I was out sick. Actually what I was
thinking of doing was to have the spreadsheet open with kind of a password box saying enter you ID. Our ID's are not tied to names. When I go to tools, options, general, it displays my User Name. I was thinking that if the ID they type in the box matched this User Name, it would let them in, otherwise it would display an error message. What I can't figure out is how to code it so the password entered would match the ID that is picked up in that General box. Is this possible? I assume excel picks up the User Name once the user clicks to open it. "Keith" wrote: If you are just trying to restrict each user from opening someone else's workbook, I would do the following: (1) Add a blank worksheet to each workbook (given that your workbooks already exist, I'd automate all of this- at least on our servers, remote accessing workbooks manually takes too long) and on that sheet, add text that says something to the effect of "either you are not the target user of this workbook, or you do not have macros enabled...blah blah blah". Have all of your other sheets veryhidden at this point. (2) In the workbook open event, check the username (LanID code below) and if it matches the name of the workbook (presumes your workbook names are based on LanID, e.g. JGESHAR.xls), automatically unhide the sheet(s) they need to use, and hide the coversheet. In the workbook close event, make all sheets veryhidden except the coversheet, so that the workbook is set for the next time it is opened. That way, if the wrong user opens the workbook, they won't be able to access the links or cause problems, and still allows you to have code in a separate workbook to 'scrape' the data from the hidden sheets when you need it. Of course, there is some risk in equating "clicking on a hyperlink" with actually reading (and understanding) procedures. Also, the code here probably wouldn't be accepted by some regulatory agencies as a 'digital signature' equivalent to hardcopy signing a 'read & understand' training document. One other thought; if you wanted to get really fancy, you could use another hidden sheet to store additional information on the user's behavior- the first thing that comes to mind is each date/time that a link is clicked (presuming that /is/ equivalent to reading a procedure). That would let you know if they are reading it within the timelines specified by their curriculum (e.g. every 6 months or 12 months or whatever), the amount of time between readings, and if certain procedures are read more frequently than required, that would be good data to indicate that a procedure is complicated and might benefit from a process improvement (six sigma, lean, etc.) project. Another thing you could do is use the time difference between date/time stamps to see the minimum amount of time someone had a link open before clicking on the next one. While they could be opening them to print and read later, it would at least alert you to the possibility of folks who might be opening and clicking just to get their "count" (as you described it) up, by clicking and closing each one, as opposed to the user who has a gap of 10, 20, or 30+ minutes between each one, showing that they had each procedure open long enough to have actually read it before clicking the next one. HTH, Keith "RocketMan" wrote in message ... Each individual workbook has hyperlinks to procedures and a counter next to the hyperlink. The workbooks are in SharePoint. We want to monitor these people and make sure they read procedures. I don't want someone opening another persons workbook. This looks like it will work, I will give it a try. If you have another idea how to do this let me know. People don't know each others user IDs. Thank a lot Keith. "Keith" wrote: Just to be clear...you want a central point of access to link users to their individual 110 workbooks? Or do you need restricted access within each of the workbooks? Personally, I wouldn't require an extra password, I think if you know who it is from the username, you don't really need a password unless you have very sensitive data, or a high risk of someone being on another person's PC and trying to open this file (in our organization we don't have that risk) In any event, try something like LanID = UCase(VBA.Environ("UserName")) to get your username to open an individual's workbook, assuming it is named with their username, you could use something like: 'Check to see if the individual workbook exists Dim fFileExists As Boolean fFileExists = (Len(Dir(MyPath & LanID & ".xls")) 0) If fFileExists = True Then 'Check to see if individual file is already open On Error Resume Next Windows(LanID & ".xls").Activate If Err < 0 Then 'file wasn't found Err = 0 'reset err code Set RnRUserWkbk = Workbooks.Open(MyPath & LanID & ".xls", False, False) Else Workbooks(LanID & ".xls").Activate End If On Error GoTo 0 Else 'No File Found MsgBox "The system was unable to locate an existing workbook for this user" End If Once you have their username, you can use that directly to control access to whatever you want. I have one workbook where I hide and show sheets based on the username of the person opening the workbook (so each person only sees certain sheets). You could use the code snippet aboveto open other workbooks- for example, everyone has a shortcut to a master workbook, which has code to open their individual workbook, then close itself. HTH, Keith "RocketMan" wrote in message ... I have 110 workbooks with individual data for people to use, I've been trying to use the password box to create passwords that tie it to their user ID so I don't have to make up 110 passwords. Can this be done? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Password / Filter workbook by user | Excel Discussion (Misc queries) | |||
Worksheet, workbook password | Excel Worksheet Functions | |||
Password not working on exported Workbook / Worksheet. | Excel Programming | |||
Why am I prompted for the workbook password for each worksheet? | Excel Worksheet Functions | |||
How to prompt user to set password that protects their worksheet? | Excel Programming |