show ms box if the xls file is opened...

[luca90]

i have a .xls file into a dir server, with all free permission on this dir, inested the tipical ms message "file is alreday opend from..." when a user open a xls file if other user have opened the same, is possible:

to show a mshbox:

Attention this file is in use from & "name of user have opoened the file for first" click on msg box close the .xls file...

i use this function to know wich user have opened the .xls file:

Sub USER_NAME()
Dim wshNet
ThisWorkbook.Activate
Sheets("RATE").Select
Set wshNet = CreateObject("WScript.Network")
wshGetUserName = wshNet.UserName
Set wshNet = Nothing
Range("A1") = ""
Range("A1") = UCase(wshGetUserName)
End Sub

[dglienna]

I don't think you want to close a file that someone has been working on. It would cause them to lose their work!

[jcis]

I think luca90 just want to show the msgbox, the same way that Excel does,
and if the file is opened by another user then show the msgbox with the name of that user and close. He don't want to close the file opened by the other user, he wants to close the file in the local machine, finally it would be closing the Excel App I think.

Excel also gives the posibility of opening a new copy of the workbook.

[luca90]

I think luca90 just want to show the msgbox, the same way that Excel does,
and if the file is opened by another user then show the msgbox with the name of that user and close. He don't want to close the file opened by the other user, he wants to close the file in the local machine, finally it would be closing the Excel App I think.

Excel also gives the posibility of opening a new copy of the workbook.

Yes.... perfect!
how to do this?

[jcis]

Not much in the forums about this, the closer I could get with some searches:

Look here post #3, by Robdog:
Another thread

[jcis]

Maybe you could use GetUserName API and comapare with the names you already have, if there is an user using the Workbook, and his Username is diferent than the active username you could show that username in the Msgbox as you want, and close the workbook.

GetUserName Example, Im not sure if this is in the same format that the usernames you already have..

Option Explicit
Private Declare Function GetUserName Lib "advapi32.dll" _ 
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Private Function GetActiveUserName() as String
Dim UserName As String, nSize As Long
 
    Call GetUserName(vbNullString, nSize)
    UserName = Space(nSize)
    Call GetUserName(UserName, nSize)
    GetActiveUserName = UserName
 
End Function

[luca90]

Maybe you could use GetUserName API and comapare with the names you already have, if there is an user using the Workbook, and his Username is diferent than the active username you could show that username in the Msgbox as you want, and close the workbook.

GetUserName Example, Im not sure if this is in the same format that the usernames you already have..

Option Explicit
Private Declare Function GetUserName Lib "advapi32.dll" _ 
Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Private Function GetActiveUserName() as String
Dim UserName As String, nSize As Long
 
    Call GetUserName(vbNullString, nSize)
    UserName = Space(nSize)
    Call GetUserName(UserName, nSize)
    GetActiveUserName = UserName
 
End Function

not for me...;-(
Example please, tks.

[jcis]

what? GetUserName doesn't work?

[luca90]

what? GetUserName doesn't work?

...no work fine
But not have a knoldedgmet to make the vba code:-(

[jcis]

I really don't know much about VBA. Maybe someone who knows, see this post now. By the way, there is a VBA forum too.

[RobDog888]

I dont quite get the problem as Excel displays a dialog prompt if you attempt to open a file that is already opened by another user. It gives you the Notify or Open as Read-Only options. It also identifies the user that has it opened first (exclusively).