Done this but now have other problem. Please see newer thread with name
Global Variable.
--
Regards and Thanks for any assistance.

Francis Brown.


"Chip Pearson" wrote:

> Get rid of the 'Set' keyword wherever you have it. 'Set' is used
> only for object type variables, not simple variables.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
> "Francis Brown" <FrancisBrown@discussions.microsoft.com> wrote in
> message
> news:B47F619D-3C4C-4E85-AB73-44B81D5DB8D4@microsoft.com...
> > I'M still having a little trouble. Here is my code
> >
> > Public name As String
> > Public team As String
> > Public TeamLeader As String
> > Public CommandN As String
> >
> >
> >
> > Public Sub FindUser()
> > currentuser = Application.UserName
> > Workbooks.Open "Z:/Systemdown/Userfile.xls"
> > lastrow =
> > Workbooks("Userfile.xls").Sheets("UserData").Range("A65536").End(xlUp).Row
> >
> > For Each person In
> > Workbooks("Userfile.xls").Sheets("UserData").Range("A2:A"
> > & lastrow)
> > If person.Value = currentuser Then
> > Set name = person.Offset(0, 1).Value
> > Set team = person.Offset(0, 2).Value
> >
> > lastrow2 =
> > Workbooks("Userfile.xls").Sheets("UserData").Range("A65536").End(xlUp).Row
> > For Each Teamgroup In
> > Workbooks("Userfile.xls").Sheets("Command").Range("A2:A" &
> > lastrow2)
> > If Teamgroup.Value = team Then
> > Set TeamLeader = Teamgroup.Offset(0, 1).Value
> >
> > Set CommandN = Teamgroup.Offset(0, 2).Value
> > Else
> > End If
> > Next Teamgroup
> > Else
> > End If
> > Next person
> > Workbooks("Userfile.xls").Close
> >
> > End Sub
> >
> > The user file has two sheets.
> >
> > Userdata with colums ID, Name and Team
> > Command with Team, Team Leader and Command.
> >
> > The Macro is meant to use application.UserName to find who is
> > currently
> > runing the program. It is then meant to look up from the tables
> > there name,
> > Team, Team Leader and Command. These are then going to used to
> > set up the
> > save directory on a shared drive for the user. That's why I
> > need the
> > variables to go Global as I need them in other modules.
> >
> > When I run the code as above I get mismatch errors when trying
> > to set the
> > data to the variable.
> >
> > Can someone suggest where i'm going wrong.
> >
> >
> > --
> > Regards and Thanks for any assistance.
> >
> > Francis Brown.
> >
> >
> > "Gary''s Student" wrote:
> >
> >> Joe is correct. If you just DIM them outside the procedures
> >> of a module,
> >> then they are by default private and only the procedures
> >> within that module
> >> can "see" them.
> >>
> >> By Declaring the PUBLIC, all procedures in all modules can see
> >> them.
> >> --
> >> Gary''s Student
> >>
> >>
> >> "Joe Fish" wrote:
> >>
> >> > Now that I'm thinking of it, I'm pretty certain it has to be
> >> > before the
> >> > first procedure in a module. So actually, it's more like:
> >> >
> >> > Public rng As Range
> >> >
> >> > Set rng = Range("A1")
> >> >
> >> > Sub MooCow ()
> >> > 'blah blah blah
> >> > End Sub
> >> >
> >> >
>
>
>