- Joined
- Nov 16, 2011
- Messages
- 2
- Reaction score
- 0
I copied code from an archived thread on this site titled "Compare Username at Workbook Open" from 2009.
The code works great when I test it out on my computer...(using Excel 2010/WIN7). When I set up for an employees computer (he is using Excel 2003/XP) the prompt box to enter the password to open the workbook comes up to get into the workbook. I do not have a password to modify in the document, just a password to open.
Is there something lost in the conversion process from a .xlsm to earlier version of excel that is causing the password not being entered? Any help will be appreciated. Here is the code below:
Option Explicit
Private Sub Workbook_Open()
Dim UserName As String
Dim V As Variant
Dim myPWD As String
Dim wkbk As Workbook
UserName = Environ("username")
myPWD = "password"
On Error Resume Next
V = Application.Match(UserName, ThisWorkbook.Worksheets("UserNames").Range("a:a"), 0)
On Error GoTo 0
If IsError(V) = True Then
MsgBox "You're not authorized!"
Else
Set wkbk = Workbooks.Open(Filename:="C:\Documents and Settings\johnsmith\Desktop\book1.xlsm", _
Password:=myPWD)
wkbk.RunAutoMacros which:=xlAutoOpen
End If
ThisWorkbook.Close savechanges:=False
End Sub
The code works great when I test it out on my computer...(using Excel 2010/WIN7). When I set up for an employees computer (he is using Excel 2003/XP) the prompt box to enter the password to open the workbook comes up to get into the workbook. I do not have a password to modify in the document, just a password to open.
Is there something lost in the conversion process from a .xlsm to earlier version of excel that is causing the password not being entered? Any help will be appreciated. Here is the code below:
Option Explicit
Private Sub Workbook_Open()
Dim UserName As String
Dim V As Variant
Dim myPWD As String
Dim wkbk As Workbook
UserName = Environ("username")
myPWD = "password"
On Error Resume Next
V = Application.Match(UserName, ThisWorkbook.Worksheets("UserNames").Range("a:a"), 0)
On Error GoTo 0
If IsError(V) = True Then
MsgBox "You're not authorized!"
Else
Set wkbk = Workbooks.Open(Filename:="C:\Documents and Settings\johnsmith\Desktop\book1.xlsm", _
Password:=myPWD)
wkbk.RunAutoMacros which:=xlAutoOpen
End If
ThisWorkbook.Close savechanges:=False
End Sub