PDA

View Full Version : Use data in txt file in Case Select statement



fendrboard
07-15-2009, 08:04 PM
Hello Folks - Im a newb to this programming stuff so forgive me for my mistakes. I have a case select statement that reads a users network name and assigns "rights" based on what level i have given them. Currently i list all the cases in the case statement but as the userlist grows it is getting out of control. I was thinking i could put the users list in a text file and it could be opened and read for the brief amount of time to set the appropriate level. Or if there is a better way to do this it would be more appreciated!

This is the current way i have it setup. Id like to incorporate the body of the case statment into a text file.


Select Case wshnetwork.UserName
Case "user1"
UserEmail = user1@domain.com
Worksheets("SBR").Range("UserType").Value = "DomainAdmin"
Case "user2"
UserEmail = user2@domain.com
Worksheets("SBR").Range("UserType").Value = "Admin"
Case "user3"
UserEmail = user3@domain.com
Worksheets("SBR").Range("UserType").Value = "MIR"
etc...
End Select

Thank you

rbrhodes
07-15-2009, 09:53 PM
Hi fb,

I would put the data on a sheet in the Workbook and hide it with code. See the sub 'Admin' on how to really hide a sheet.

Then the code could call the data from the hidden sheet.

The hidden sheet could be updated only by someone with the password (which is stored on the hidden sheet).


- Insert a sheet and name it Admin.

On the Admin Sheet:

- Put the password in "D2"

- Usernames in A1 - A#

- Rights in B1 - B#

- Email address in C1 - C#


-Copy this code to a Module to hide/Unhide the Admin sheet


Option Explicit
Sub Admin()
Dim pword As String
Dim ws As Worksheet

'Put the Sheet name here
Set ws = Sheets("Admin")
With ws
If .Visible = True Then
.Visible = xlVeryHidden
Else
'Request password
pword = InputBox("Enter password.", "Admin password required")
'Password is on hidden sheet
If pword = .Range("D2") Then
.Visible = True
.Activate
End If
End If
End With

End Sub



This code will find the Username in Col A of sheet "Admin", then return the Rights to the named range on Sheet SBR and load the variable UserEmail from Col C.


'These are already done somewhere...
Dim UserName
Dim UserEmail
Dim wshnetwork

'//This replaces your Select Case routine
Dim msg As Long
Dim FindMe As Long
Dim ws As Worksheet

'Sheet name here
Set ws = Sheets("Admin")
With ws
'Allow not found error
On Error Resume Next
'Find Username on secret sheet
FindMe = .Columns("A:A").Find(What:=UserName, After:=.Range("A1"), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
If Err = 0 Then
'Found. Get Rights from Column B
Worksheets("SBR").Range("UserType") = .Cells(FindMe, 2)
'Get Email from Column C
UserEmail = .Cells(FindMe, 3)
Else
msg = MsgBox("Username " & UserName & " not found!", vbExclamation, "Houston...")
End If
End With
Set ws = Nothing
On Error GoTo 0
'//End replace

fendrboard
07-16-2009, 01:11 PM
Hello dr, thanks for the quick reply. However im not trying to lock down via password but rather letting people view different worksheets when a user opens the workbook. So if a user opens the book with "MIR" rights, they only see the MIR tab and ect ect...I am using these workbooks to track the building of servers. Every server has an excel workbook associated with it and of course all the coding resides within that workbook. The reason why i wanted use an external txt file is because i could just update the text file and that in turn would update all of the workbooks that access that txt file. This would simplify my process instead of going back through all of them (which i wont do anyways). Any ideas?

rbrhodes
07-16-2009, 02:45 PM
Hi again,

Here's an example sheet with code which you'll have to incorporate into yours to replace the Select Case structure. Also a 1 sheet excel file containing the list of users, email addresses and rights.

The code is in WB UserListCode. It:

- Checks if UserName is not "" (blank), bails if is blank
- Opens the workbook "UserList"
- Finds Username and does the data xfer
- Closes the workbook "UserList"

*Tosses up an error message if "User" was not found

Save the "UserList" workbook somewhere convenient.

Incorporate the code into your original Sub, removing the Select Case, of course.

Change the path name to the "UserList" workbook, and the sheet name if you changed it.

fendrboard
07-16-2009, 06:47 PM
DR - cant thank you enough!!! Worked like a charm right out of the box. Now i dont have to worry about the permissions being attached to one particular file. Many many thanks.

rbrhodes
07-16-2009, 11:55 PM
Allright!!