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?

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" <(E-Mail Removed)> wrote in message
news:629B4DF5-8491-4974-995D-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> wrote in message
> news:629B4DF5-8491-4974-995D-(E-Mail Removed)...
> >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?
>
>
>

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" <(E-Mail Removed)> wrote in message
news503340A-8933-4C69-9272-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:629B4DF5-8491-4974-995D-(E-Mail Removed)...
>> >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?
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news503340A-8933-4C69-9272-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:629B4DF5-8491-4974-995D-(E-Mail Removed)...
> >> >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?
> >>
> >>
> >>
>
>
>