Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is something wrong with the code
drop the GOTO - its unnecesary
Also you unprotect the sheet before the IF statement. drop this too. Your IF handles protection. now this line: If username = "ABC" Or "CDE" Or "EFG" Or "GHI" Then evaluates incorrectly. If (username = "ABC") Or (username ="CDE") Or (username ="EFG") Or (username ="GHI") Then This is clearly a case where a SELECT CASE staement would be so much clearer... Sub auto_open() On Error Resume Next Dim username As String username = GetUserName ActiveWorkbook.BuiltinDocumentProperties("Author") = _ username SELECT CASE Username CASE "ABC","CDE","EFG","GHI" WITH Worksheets("Sheet1") .Unprotect .Cells.Locked = True .Range("a3").Select END WITH CASE ELSE ActiveSheet.Protect Contents:=True END SELECT END SUB Note that you test for those that CAN edit the sheet. I'd assume that nobody else can. In you IF what happens if a username doesn't fall into EITHER category... you've unprotected the sheet, so you're giving access. In my code, if a user can edit, then the sheet is unprotected, it is protected for everyone else. think clearly about logic. use a diagram if it helps. with IF statements, there's no need for GOTO statements. in fact, there's almost no reason to use goto's except for error handling. (let's not debate inferred goto's ok?) Patrick Molloy Microsoft Excel MVP -----Original Message----- Hi all, I write an Marco that will protect the columns based on User ID from Windows NT. I find the IF...THEN statement is not working properly. It unprotects the sheet no matter which User ID I'm using. Are there any errors on this Macro? thank you Option Explicit Declare Function Get_User_Name Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Function GetUserName() As String Application.Volatile Dim lpBuff As String * 25 Get_User_Name lpBuff, 25 GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) End Function Sub auto_open() On Error Resume Next Dim username As String Sheets("username").Visible = True Sheets("username").Select Range("A1").Select username = GetUserName Sheets("username").Visible = False Sheets("CVD Progress Register").Select Range("A1").Select ActiveWorkbook.BuiltinDocumentProperties("Author" ) = username Cells.Select Worksheets("Sheet1").Unprotect Selection.Locked = True Range("a3").Select If username = "ABC" Or "CDE" Or "EFG" Or "GHI" Then Cells.Select Worksheets("Sheet1").Unprotect Selection.Locked = True Range("a3").Select GoTo leave ElseIf username = "IJK" Or "KLM" Or "MNO" Or "OPQ" Then Range("M:M,N:N,U:U,AC:AC").Select Selection.Locked = False ActiveSheet.Protect Contents:=True leave: End If End Sub -- Join the CISSP Hong Kong Study Group http://hk.groups.yahoo.com/group/Hongkong_CISSP/ . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is something wrong with the code
Thank you Patrick.
Your statement is more clear and simple. "Patrick Molloy" wrote in message ... drop the GOTO - its unnecesary Also you unprotect the sheet before the IF statement. drop this too. Your IF handles protection. now this line: If username = "ABC" Or "CDE" Or "EFG" Or "GHI" Then evaluates incorrectly. If (username = "ABC") Or (username ="CDE") Or (username ="EFG") Or (username ="GHI") Then This is clearly a case where a SELECT CASE staement would be so much clearer... Sub auto_open() On Error Resume Next Dim username As String username = GetUserName ActiveWorkbook.BuiltinDocumentProperties("Author") = _ username SELECT CASE Username CASE "ABC","CDE","EFG","GHI" WITH Worksheets("Sheet1") .Unprotect .Cells.Locked = True .Range("a3").Select END WITH CASE ELSE ActiveSheet.Protect Contents:=True END SELECT END SUB Note that you test for those that CAN edit the sheet. I'd assume that nobody else can. In you IF what happens if a username doesn't fall into EITHER category... you've unprotected the sheet, so you're giving access. In my code, if a user can edit, then the sheet is unprotected, it is protected for everyone else. think clearly about logic. use a diagram if it helps. with IF statements, there's no need for GOTO statements. in fact, there's almost no reason to use goto's except for error handling. (let's not debate inferred goto's ok?) Patrick Molloy Microsoft Excel MVP -----Original Message----- Hi all, I write an Marco that will protect the columns based on User ID from Windows NT. I find the IF...THEN statement is not working properly. It unprotects the sheet no matter which User ID I'm using. Are there any errors on this Macro? thank you Option Explicit Declare Function Get_User_Name Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Function GetUserName() As String Application.Volatile Dim lpBuff As String * 25 Get_User_Name lpBuff, 25 GetUserName = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) End Function Sub auto_open() On Error Resume Next Dim username As String Sheets("username").Visible = True Sheets("username").Select Range("A1").Select username = GetUserName Sheets("username").Visible = False Sheets("CVD Progress Register").Select Range("A1").Select ActiveWorkbook.BuiltinDocumentProperties("Author" ) = username Cells.Select Worksheets("Sheet1").Unprotect Selection.Locked = True Range("a3").Select If username = "ABC" Or "CDE" Or "EFG" Or "GHI" Then Cells.Select Worksheets("Sheet1").Unprotect Selection.Locked = True Range("a3").Select GoTo leave ElseIf username = "IJK" Or "KLM" Or "MNO" Or "OPQ" Then Range("M:M,N:N,U:U,AC:AC").Select Selection.Locked = False ActiveSheet.Protect Contents:=True leave: End If End Sub -- Join the CISSP Hong Kong Study Group http://hk.groups.yahoo.com/group/Hongkong_CISSP/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What is wrong with this code? | Excel Discussion (Misc queries) | |||
What is wrong with the code? | Excel Discussion (Misc queries) | |||
Can someone tell me what is wrong with this code? | Excel Discussion (Misc queries) | |||
What's wrong with my code ? | Excel Worksheet Functions |