Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27
Default 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   Report Post  
Posted to microsoft.public.excel.programming
Pro Pro is offline
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What is wrong with this code? Ayo Excel Discussion (Misc queries) 14 June 10th 08 03:09 AM
What is wrong with this code? jlclyde Excel Discussion (Misc queries) 5 January 9th 08 06:12 PM
What is wrong with the code? Eric Excel Discussion (Misc queries) 2 September 13th 07 10:36 AM
Can someone tell me what is wrong with this code? Ant Excel Discussion (Misc queries) 8 November 14th 05 03:53 PM
What's wrong with my code ? christophe meresse Excel Worksheet Functions 3 August 2nd 05 05:09 PM


All times are GMT +1. The time now is 11:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"