Comapre columns

[kevin carter]

hi i have a worksheet containing two columns of data
column A = names
column B = Team

Cell k1 holds the widows log on name ie kevin

Cell I1 holds the team identification ie team 1

i want to read column A compare it with the name logged on
then i want to check the team in column B against team Identification

if the user name is correct and the team is correct
open the workbook for editing

if the username is correct and the team is incorrect open the
open the book in Read Only

If user name is incorrect
open workbook Read Only

 

[Alan]

hi i have a worksheet containing two columns of data
column A   =   names
column B  =    Team

Cell k1 holds the widows log on name ie kevin

Cell I1 holds the team identification  ie team 1

i want to read column A compare it with the name logged on
then i want to check the team in column B against team Identification

if the user name is correct and the team is correct
open the workbook for editing

if the username is correct and the team is incorrect open the
open the book in Read Only

If user name is incorrect
open workbook  Read Only

How do you get the user name and team into cells K1 and L1 before the
file opens?

A.

 

[GS]

kevin carter has brought this to us :

hi i have a worksheet containing two columns of data
column A = names
column B = Team

Cell k1 holds the widows log on name ie kevin

Cell I1 holds the team identification ie team 1

i want to read column A compare it with the name logged on
then i want to check the team in column B against team Identification

if the user name is correct and the team is correct
open the workbook for editing

if the username is correct and the team is incorrect open the
open the book in Read Only

If user name is incorrect
open workbook Read Only

This doesn't make any sense! How can you open a workbook that's already
open?

I suggest you change your approach to using a login form [sheet] that
hides/unhides the various sheets appropriately, AND if the logged in
user is not allowed to edit then add protection to those sheets.

 

[Alan]

kevin carter has brought this to us :

hi i have a worksheet containing two columns of data
column A   =   names
column B  =    Team

Cell k1 holds the widows log on name ie kevin

Cell I1 holds the team identification  ie team 1

i want to read column A compare it with the name logged on
then i want to check the team in column B against team Identification

if the user name is correct and the team is correct
open the workbook for editing

if the username is correct and the team is incorrect open the
open the book in Read Only

If user name is incorrect
open workbook  Read Only

This doesn't make any sense! How can you open a workbook that's already
open?

I suggest you change your approach to using a login form [sheet] that
hides/unhides the various sheets appropriately, AND if the logged in
user is not allowed to edit then add protection to those sheets.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

I think that I disagree with Garry. Your idea could make sense if you
want the "occasional user" to be able to use the workbook but not be
able to save any changes made. It's not a matter of opening a workbook
that is already open but rather using the Workbook_Open event to set
the readonly attribute before the user has access. This is a
relatively simple matter but, from the way you describe the problem,
the event will need to know the user name and the user's team which
will probably not be available from the two cells you mention until
the workbook is up and running ... I think this may be where Garry is
coming from. However ...

You can pick up the user identity automatically (Application.UserName)
and compare that to a team list embedded within the workbook using
vlookup or you could use a user form which asks the user to enter his/
her name and team ... which could be written to the cells you mention
but why bother? - the comparison to authorised lists can be done
without this step. Once you have this information available to the
opening event changing the readonly attribute is a piece of cake.

A.

 

[Alan]

kevin carter has brought this to us :

This doesn't make any sense! How can you open a workbook that's already
open?

I suggest you change your approach to using a login form [sheet] that
hides/unhides the various sheets appropriately, AND if the logged in
user is not allowed to edit then add protection to those sheets.
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

I think that I disagree with Garry. Your idea could make sense if you
want the "occasional user" to be able to use the workbook but not be
able to save any changes made. It's not a matter of opening a workbook
that is already open but rather using the Workbook_Open event to set
the readonly attribute before the user has access. This is a
relatively simple matter but, from the way you describe the problem,
the event will need to know the user name and the user's team which
will probably not be available from the two cells you mention until
the workbook is up and running ... I think this may be where Garry is
coming from. However ...

You can pick up the user identity automatically (Application.UserName)
and compare that to a team list embedded within the workbook using
vlookup or you could use a user form which asks the user to enter his/
her name and team ... which could be written to the cells you mention
but why bother? - the comparison to authorised lists can be done
without this step. Once you have this information available to the
opening event changing the readonly attribute is a piece of cake.

A.- Hide quoted text -

- Show quoted text -

On further thought ... why does the team name matter? Surely, if the
user is authorised, the user is authorised [full stop].

On the basis of this you could use:

Private Sub Workbook_Open()

Dim TestMatch As Long

TestMatch = 0

On Error GoTo NoMatch
TestMatch = WorksheetFunction.Match(Application.UserName,
Columns("A:A"), 0)
On Error GoTo 0
SetAttr Pathname:=ThisWorkbook.FullName, Attributes:=vbNormal
Exit Sub

NoMatch:
SetAttr Pathname:=ThisWorkbook.FullName, Attributes:=vbReadOnly

End Sub

To use this there must be a list of user names in column A.

A.