I'm trying to create an excel template that will require users login inorder
to use the spread sheet. How would I go about doing that? Any help will be
great. Thanks

Excel security is easily defeated at the best of time so anything you do can
be defeated if your end users are motivated to do so. That being said, my
preference is to create a list of allowed users on a hidden sheet within the
book. I then use the Workbook_Open event to check the users login name
against the list of allowed names. Something like this...

Private Sub Workbook_Open()
Dim rng As Range

Set rng = Sheets("Sheet1").Cells.Find(What:=Environ.UserName , _
LookAt:=xlWhole, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox "The name was not found. Time to close the book."
ThisWorkbook.Close False
End If
End Sub

Note that it is looking for your windows login name... I like this method
because it is easy and you do not have to remember passwords and such. If you
want a password solution then I would recommend taking the excel file and
doing a save as and then using Tools - General Options to attach a password
(which is actually very good protection).
--
HTH...

Jim Thomlinson


"slacky75" wrote:

I'm trying to create an excel template that will require users login inorder
to use the spread sheet. How would I go about doing that? Any help will be
great. Thanks

Thanks Jim. I tried your codes but had to change a typo
(Environ("USERNAME")). In any case, the codes you provided works by denying
access to the workbook for users not on the list.

I guess i was a bit vague in my question. I want a pop up window asking for
login name and password. can you help me with this?

Thanks

"Jim Thomlinson" wrote:

Excel security is easily defeated at the best of time so anything you do can
be defeated if your end users are motivated to do so. That being said, my
preference is to create a list of allowed users on a hidden sheet within the
book. I then use the Workbook_Open event to check the users login name
against the list of allowed names. Something like this...

Private Sub Workbook_Open()
Dim rng As Range

Set rng = Sheets("Sheet1").Cells.Find(What:=Environ.UserName , _
LookAt:=xlWhole, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox "The name was not found. Time to close the book."
ThisWorkbook.Close False
End If
End Sub

Note that it is looking for your windows login name... I like this method
because it is easy and you do not have to remember passwords and such. If you
want a password solution then I would recommend taking the excel file and
doing a save as and then using Tools - General Options to attach a password
(which is actually very good protection).
--
HTH...

Jim Thomlinson


"slacky75" wrote:

I'm trying to create an excel template that will require users login inorder
to use the spread sheet. How would I go about doing that? Any help will be
great. Thanks

You are absolutely correct on the environ("UserName")... sorry...

For the password thing you need to add a text boxes to a user form. The
biggest trick is to change the PasswordChar Property to * (if this does not
make sense to me just reply back for further info.

Now add a command button to the form. The code for the button will be
something like

if TextBox1.text = "MyPassword" then
unload me
else
msgBox "Password Incorrect. Please try again."
endif

You also need to handle the deactivate and terminate events to validate the
passowrd. Finally you need to give them a way to get if they do not have a
password such that the workbook closes. If you need more help with that just
let me know...
--
HTH...

Jim Thomlinson


"slacky75" wrote:

Thanks Jim. I tried your codes but had to change a typo
(Environ("USERNAME")). In any case, the codes you provided works by denying
access to the workbook for users not on the list.

I guess i was a bit vague in my question. I want a pop up window asking for
login name and password. can you help me with this?

Thanks

"Jim Thomlinson" wrote:

Excel security is easily defeated at the best of time so anything you do can
be defeated if your end users are motivated to do so. That being said, my
preference is to create a list of allowed users on a hidden sheet within the
book. I then use the Workbook_Open event to check the users login name
against the list of allowed names. Something like this...

Private Sub Workbook_Open()
Dim rng As Range

Set rng = Sheets("Sheet1").Cells.Find(What:=Environ.UserName , _
LookAt:=xlWhole, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox "The name was not found. Time to close the book."
ThisWorkbook.Close False
End If
End Sub

Note that it is looking for your windows login name... I like this method
because it is easy and you do not have to remember passwords and such. If you
want a password solution then I would recommend taking the excel file and
doing a save as and then using Tools - General Options to attach a password
(which is actually very good protection).
--
HTH...

Jim Thomlinson


"slacky75" wrote:

I'm trying to create an excel template that will require users login inorder
to use the spread sheet. How would I go about doing that? Any help will be
great. Thanks

thanks for helping me Jim.

I must be way over my head with this. How do I incorporate this to the
codes you provide yesterday? can you give me a sample code?

thanks

"Jim Thomlinson" wrote:

You are absolutely correct on the environ("UserName")... sorry...

For the password thing you need to add a text boxes to a user form. The
biggest trick is to change the PasswordChar Property to * (if this does not
make sense to me just reply back for further info.

Now add a command button to the form. The code for the button will be
something like

if TextBox1.text = "MyPassword" then
unload me
else
msgBox "Password Incorrect. Please try again."
endif

You also need to handle the deactivate and terminate events to validate the
passowrd. Finally you need to give them a way to get if they do not have a
password such that the workbook closes. If you need more help with that just
let me know...
--
HTH...

Jim Thomlinson


"slacky75" wrote:

Thanks Jim. I tried your codes but had to change a typo
(Environ("USERNAME")). In any case, the codes you provided works by denying
access to the workbook for users not on the list.

I guess i was a bit vague in my question. I want a pop up window asking for
login name and password. can you help me with this?

Thanks

"Jim Thomlinson" wrote:

Excel security is easily defeated at the best of time so anything you do can
be defeated if your end users are motivated to do so. That being said, my
preference is to create a list of allowed users on a hidden sheet within the
book. I then use the Workbook_Open event to check the users login name
against the list of allowed names. Something like this...

Private Sub Workbook_Open()
Dim rng As Range

Set rng = Sheets("Sheet1").Cells.Find(What:=Environ.UserName , _
LookAt:=xlWhole, _
MatchCase:=False)
If rng Is Nothing Then
MsgBox "The name was not found. Time to close the book."
ThisWorkbook.Close False
End If
End Sub

Note that it is looking for your windows login name... I like this method
because it is easy and you do not have to remember passwords and such. If you
want a password solution then I would recommend taking the excel file and
doing a save as and then using Tools - General Options to attach a password
(which is actually very good protection).
--
HTH...

Jim Thomlinson


"slacky75" wrote:

I'm trying to create an excel template that will require users login inorder
to use the spread sheet. How would I go about doing that? Any help will be
great. Thanks