Unable to Open File Error

M

Michael Lockwoo

Ok, Ladies and Gentlemen,

I'm at my wits end with this.

I have a excel file that uses the Workbook_open event to pop up a
inputbox asking for the individual's initials so I can log their
activity. This is placed in a hidden cell and used to call at will.

With that input box, when the file is opened, an error comes up telling
me it's unable to open/read the file and then repairs it and strips my
pivottable out of the worksheet.

Without the inputbox, the file works fine, as intended.

Any ideas?

Michael J. Lockwood
 
J

JLatham

Can you cut and paste the _Open event code you are using here?

This works fine for me as a test case:

Private Sub Workbook_Open()
Dim WhoAmI As String
WhoAmI = Application.InputBox("Enter your initials", "Log In Please")
Worksheets("Sheet1").Range("A1") = WhoAmI
End Sub

it even works if written more compactly as:

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1") = Application.InputBox("Enter your
initials", "Log In Please")
End Sub

so we need to see what might be strange in your code.
 
D

Dave Peterson

I don't think I'd bother the user.

Maybe you can use the application.username (although sometimes it says important
things like: "Valued Gateway customer"!)

Or you could use the network id.

Maybe you can take some stuff from this:

Option Explicit
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function
Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 255
strCompName = String$(lngLen - 1, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function
Sub auto_open()
Dim DestCell As Range

With Worksheets("HiddenLogSheetNameHere")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With DestCell
.Value = fOSUserName
.Offset(0, 1).Value = Application.UserName
.Offset(0, 2).Value = fOSMachineName
With .Offset(0, 3)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With
End Sub
 
Top