Any way to be notified if someone else is in the same sheet?

[JoAnn]

We have a spreadsheet used by multiple people from within MS Sharepoint.

1st ... I'm looking for a way to generate a message that tells the user that
someone else is in the spreadsheet as soon as they open it. My current plan
is to tell them it's in use & to enter their data then use File >Send to to
send it to me for later posting.

All files automatically appear with a "READ ONLY" indicator when we open
them from within Sharepoint, so I can't rely on that as an indicator.

2nd ... I would ultimately like to create some kind of temporary
collection/storage sheet or form that allows users to enter their information
& save it individually until it can be merged with the real spreadsheet.

Preferably something that can be accessed by multiple users & generate
individual docs/sheets -- like Word users clicking a template & generating an
individual doc off the template instead of the template itself.

Does anyone have any idea how I can do either of these things? I've written
some macros & created user forms in Word but nothing in Excel yet.

Thanks for your help!

 

[Nigel]

Check the features and functions of SharePoint, I am pretty sure you can do
all you want from there. You might need to get your IS department to set it
up if you are on a business network.

 

[JoAnn]

Thanks for your reply ... unfortunately, we have just started
customizing/using Sharepoint & our IT resources are tied up with other
Sharepoint issues that take higher precedence ... bottom line, they won't be
able to get to it for months.

Any way to do what I want from within Excel? Even just the 1st piece would
be a big help to us.

 

[Nigel]

' This function checks to see if a file is open or not. If the file is

' already open, it returns True. If the file is not open, it returns

' False. Otherwise, a run-time error will occur because there is

' some other problem accessing the file.



Function IsFileOpen(filename As String)

Dim filenum As Integer, errnum As Integer



On Error Resume Next ' Turn error checking off.

filenum = FreeFile() ' Get a free file number.

' Attempt to open the file and lock it.

Open filename For Input Lock Read As #filenum

Close filenum ' Close the file.

errnum = Err ' Save the error number that occurred.

On Error GoTo 0 ' Turn error checking back on.



' Check to see which error occurred.

Select Case errnum



' No error occurred.

' File is NOT already open by another user.

Case 0

IsFileOpen = False



' Error number for "Permission Denied."

' File is already opened by another user.

Case 70

IsFileOpen = True



' Another error occurred.

Case Else

Error errnum

End Select

End Function




--

Regards,
Nigel
(e-mail address removed)

Thanks for your reply ... unfortunately, we have just started
customizing/using Sharepoint & our IT resources are tied up with other
Sharepoint issues that take higher precedence ... bottom line, they won't
be
able to get to it for months.

Any way to do what I want from within Excel? Even just the 1st piece
would
be a big help to us.