Global Variable

[Francis Brown]

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.

[Leith Ross]

Hello Francis,

Remove the Set statements from you variables name, team, TeamLeader, and CommandN.

Sincerely,
Leith Ross

[Francis Brown]

Thanks For answer.

That problem is cleared.

I got another problem with declaring the variable name in the workbook
module. When the program to set the variables was called it through up a
problem about setting a variable form which the module was derived. I changed
the variable name to namex and this resolved. Although I could not find the
other declaration the error was talking about.

However I have the following problem

My program to set the variables works but when I run a small test program
imedietly after the first with MSGBOX commands for all the variables they
come up empty.

any ideas.
--
Regards and Thanks for any assistance.

Francis Brown.


"Leith Ross" wrote:

>
> Hello Francis,
>
> Remove the Set statements from you variables name, team, TeamLeader,
> and CommandN.
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=488468
>
>

[Chip Pearson]

> I got another problem with declaring the variable name in the
> workbook
> module.

Unless you have good reason to do so, don't declare the variables
in the ThisWorkbook module. Declare all your global variables in
a standard code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Francis Brown" <[email protected]> wrote in
message
news:[email protected]...
> Thanks For answer.
>
> That problem is cleared.
>
> I got another problem with declaring the variable name in the
> workbook
> module. When the program to set the variables was called it
> through up a
> problem about setting a variable form which the module was
> derived. I changed
> the variable name to namex and this resolved. Although I could
> not find the
> other declaration the error was talking about.
>
> However I have the following problem
>
> My program to set the variables works but when I run a small
> test program
> imedietly after the first with MSGBOX commands for all the
> variables they
> come up empty.
>
> any ideas.
> --
> Regards and Thanks for any assistance.
>
> Francis Brown.
>
>
> "Leith Ross" wrote:
>
>>
>> Hello Francis,
>>
>> Remove the Set statements from you variables name, team,
>> TeamLeader,
>> and CommandN.
>>
>> Sincerely,
>> Leith Ross
>>
>>
>> --
>> Leith Ross
>> ------------------------------------------------------------------------
>> Leith Ross's Profile:
>> http://www.excelforum.com/member.php...o&userid=18465
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=488468
>>
>>

[Francis Brown]

This solved problem.

Regards to all for help.
--
Regards and Thanks for any assistance.

Francis Brown.


"Chip Pearson" wrote:

> > I got another problem with declaring the variable name in the
> > workbook
> > module.
>
> Unless you have good reason to do so, don't declare the variables
> in the ThisWorkbook module. Declare all your global variables in
> a standard code module.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Francis Brown" <[email protected]> wrote in
> message
> news:[email protected]...
> > Thanks For answer.
> >
> > That problem is cleared.
> >
> > I got another problem with declaring the variable name in the
> > workbook
> > module. When the program to set the variables was called it
> > through up a
> > problem about setting a variable form which the module was
> > derived. I changed
> > the variable name to namex and this resolved. Although I could
> > not find the
> > other declaration the error was talking about.
> >
> > However I have the following problem
> >
> > My program to set the variables works but when I run a small
> > test program
> > imedietly after the first with MSGBOX commands for all the
> > variables they
> > come up empty.
> >
> > any ideas.
> > --
> > Regards and Thanks for any assistance.
> >
> > Francis Brown.
> >
> >
> > "Leith Ross" wrote:
> >
> >>
> >> Hello Francis,
> >>
> >> Remove the Set statements from you variables name, team,
> >> TeamLeader,
> >> and CommandN.
> >>
> >> Sincerely,
> >> Leith Ross
> >>
> >>
> >> --
> >> Leith Ross
> >> ------------------------------------------------------------------------
> >> Leith Ross's Profile:
> >> http://www.excelforum.com/member.php...o&userid=18465
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=488468
> >>
> >>
>
>
>