Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variable
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variable
Hello Francis, Remove the Set statements from you variables name, team, TeamLeader and CommandN. Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48846 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variable
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variable
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Global Variable
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Global variable | Excel Discussion (Misc queries) | |||
Global Variable | Excel Discussion (Misc queries) | |||
Global Variable | Excel Programming | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Global variable | Excel Programming |