Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 30-10-2009
Member
 
Join Date: Sep 2009
Posts: 125
Username and Password on Excel sheet

Is it possible to make the opening a excel sheet to have an ID and password? I explain, I have an Excel application in my computer in LAN. This application contains one sheet per employee. (Number 15 sheets). I wish that every employee has access only to the sheet with his name and this password. The other leaves him being hidden. Being new to add VBA is that you can give me a hand.
Reply With Quote
  #2  
Old 30-10-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: Username and Password on Excel sheet

Hello, if you are talking about "USERNAME" that will give you the windows login name of user X, then attention there is a username in VBA that only gives the name given during installation in License granted to use. If you are talking about any other "USERNAME" then you can not automate the opening via password and display the sheet on the user X

However you can do something like that:

Code:
Option Explicit 
 
Dim sUser As String 
 
Private Sub Workbook_Open() 
Dim Ws As Worksheet, i As Long 
    sUser = Environ("USERNAME" ) 
    Application.ScreenUpdating = False 
 
    For Each Ws In ThisWorkbook.Worksheets 
        If Ws.Name <> sUser Then 
            Ws.Visible = xlVeryHidden 
        Else 
            Ws.Visible = xlSheetVisible 
        End If 
    Next Ws 
 
    Application.ScreenUpdating = True 
End Sub
or
Code:
Private Sub Workbook_Open() 
Dim Ws As Worksheet, i As Long 
    sUser = Environ("USERNAME" ) 
    Application.ScreenUpdating = False 
     
    On Error Resume Next 
    For Each Ws In ThisWorkbook.Worksheets 
        Select Case sUser 
            Case "V" 
                ShUser1.Visible = xlSheetVisible 
            Case "X" 
                ShUser2.Visible = xlSheetVisible 
            Case "Y" 
                ShUser3.Visible = xlSheetVisible 
        End Select 
        Ws.Visible = xlSheetVeryHidden 
    Next Ws 
    Application.ScreenUpdating = True 
End Sub
Reply With Quote
  #3  
Old 30-10-2009
Member
 
Join Date: Sep 2009
Posts: 125
Re: Username and Password on Excel sheet

Thank you for having responded and suggested that code. I cons forgot to mention one detail that has great importance. In addition to the employee who needs access to its tab, the head of service must have access to all the tabs. I hope this does not represent a major changes in your code.
Reply With Quote
  #4  
Old 30-10-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: Username and Password on Excel sheet

In such case, the code would be:

Code:
Option Explicit 
 
Private Sub Workbook_Open() 
Dim Ws As Worksheet, i As Long 
Dim sUSer As String 
    sUSer = Environ("USERNAME" ) 
    Application.ScreenUpdating = False 
      
    On Error Resume Next 
    For Each Ws In ThisWorkbook.Worksheets 
        Select Case sUSer 
            Case "Admin" 
                ShUser1.Visible = xlSheetVisible 
                ShUser2.Visible = xlSheetVisible 
                ShUser3.Visible = xlSheetVisible 
                Exit For 
            Case "V" 
                ShUser1.Visible = xlSheetVisible 
            Case "X" 
                ShUser2.Visible = xlSheetVisible 
            Case "Y" 
                ShUser3.Visible = xlSheetVisible 
        End Select 
        Ws.Visible = xlSheetVeryHidden 
    Next Ws 
    Application.ScreenUpdating = True 
End Sub
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Username and Password on Excel sheet"
Thread Thread Starter Forum Replies Last Post
How to use Mail Merge from one Excel Sheet to another Excel Sheet Abélard MS Office Support 2 23-02-2012 02:59 PM
Need help for getting the DSL username and password Road-Runner Networking & Security 4 03-12-2010 05:18 PM
Computer keeps asking me for Username/Password Hakon Operating Systems 4 05-02-2010 09:56 AM
backup to a network drive is asking for username and password...whatusername and password? Lisa Hetherington Vista Help 7 04-02-2008 09:05 PM
Windows XP - No username/password box mcamp Operating Systems 4 22-04-2007 09:37 PM


All times are GMT +5.5. The time now is 07:46 AM.