+ Reply to Thread
Results 1 to 12 of 12

Using the Environ Function on Workbook Open

  1. #1
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Using the Environ Function on Workbook Open

    Hello,
    I have a spreadsheet which is shared accross a vast amount of users, upon first open of the document Management would populate a sheet to contain staff credentials based on thier System Login Data.

    So for instance, i logon to my machine and i have a staff number that is only allocated to me, the environ variable would read and display this, So Management would go into the document and be face with 3 Cells, Cell1 = Ny Name, Cell2 = Staff Number, Cell3 = Team i Belong to.
    So when i open the document, the environ variable would check my staff number then access the list to see who i am and where i belong then place me accordingly.
    Team1 Would only see sheet 1
    Team2 Would only see sheet 2
    and so on....

    Any Help?
    Like my post? Considered leaving Rep? Thanks.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Using the Environ Function on Workbook Open

    You could use .Find to locate your number in the table & then offset to get he relevant data
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Re: Using the Environ Function on Workbook Open

    I always blunder with the .find code not my strong point as yet...But anyway say once i find a match i .offset(0, 1) to collect the team but then how can i tell the code to place me on sheet one?

    Example?

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Using the Environ Function on Workbook Open

    I can't see why you need multiple sheets. Can you attach a dummy workbook

  5. #5
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Re: Using the Environ Function on Workbook Open

    I dont have a dummy workbook to hand because have yet to even start my project.
    It doesnt have to be sheets to be honest simplist way to explain this:
    Workbook open the Manager has 3 cells which he/she puts my name, Staff Number, Team i belong too, then a command button to submit to a list.
    Now all users who open the document start at the main page which has several command buttons, now depending on the team your allocated too as to which buttons are enabled for your use, this is where the environ comes in.

    So i load up the document the Environ checks me out then finds me on the list, then depending on my team disables all the buttons im not allowed to access.

    example:

    Please Login or Register  to view this content.
    Hopefully you get my drift?

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Using the Environ Function on Workbook Open

    maybe something like
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Re: Using the Environ Function on Workbook Open

    Thanks Roy that looks promising i will give it a while tonight sometime and get back to you

  8. #8
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Re: Using the Environ Function on Workbook Open

    I have encountered problems, please find attached workbook see if you can obtain an understanding.
    Thanks ahead
    Attached Files Attached Files

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Using the Environ Function on Workbook Open

    The CommandButtons are on Sheet 2 your code is looking for them on Sheet2, it's also looking for the user on the wrongg sheet & the offset is wrong. Edit again - it's Team 1 not Team1, etc
    Last edited by royUK; 04-05-2012 at 11:16 AM.

  10. #10
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Re: Using the Environ Function on Workbook Open

    Hello Roy,
    I amended the code and added buttons to sheet3 still getting variable not defined and error line is the first case.
    here is the code now..

    Please Login or Register  to view this content.

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Using the Environ Function on Workbook Open

    Edit your code to show which sheet has the buttons

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    10-08-2009
    Location
    Hull, England
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    73

    Re: Using the Environ Function on Workbook Open

    I have tried that, my buttons are on sheet2 my staff numbers are on sheet 3 the Team is located 1 to the right of the staff number making the offset correct. and still i get variable no defined and the first line of error i will mark by comment.
    im not sure if the .enabled function isnt allowed or something?

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1