So, what if it's already open?
 

Hi All....
I have the following macro in a program to prompt the users to occasionally
Archive the file. It works fine, however, when someone opens the file, if
another user already has it open, I would like the pop-up not to appear. How
might the below code be modified to accomplish that, please?

Private Sub Workbook_Open()
Dim MyDate
MyDate = Date
Dim LastDate
LastDate = Range("ah37").Value
ans = MsgBox("This LOGBOOK Program has not been Archived since " &
LastDate & ".....Do it now?", vbYesNo)
If ans = vbYes Then
Range("ah37").Select
Selection.Value = MyDate
Application.Run "NewSaveArchive"
Else
End If
End Sub

Thanks,
Vaya con Dios,
Chuck, CABGx3

So, what if it's already open?
 

You can check if the file is open by some other user with this approach:


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

The articles are all basically the same.

If you find the file is open, then don't offer to archive it.

--
Regards,
Tom Ogilvy



"CLR" wrote:

Hi All....
I have the following macro in a program to prompt the users to occasionally
Archive the file. It works fine, however, when someone opens the file, if
another user already has it open, I would like the pop-up not to appear. How
might the below code be modified to accomplish that, please?

Private Sub Workbook_Open()
Dim MyDate
MyDate = Date
Dim LastDate
LastDate = Range("ah37").Value
ans = MsgBox("This LOGBOOK Program has not been Archived since " &
LastDate & ".....Do it now?", vbYesNo)
If ans = vbYes Then
Range("ah37").Select
Selection.Value = MyDate
Application.Run "NewSaveArchive"
Else
End If
End Sub

Thanks,
Vaya con Dios,
Chuck, CABGx3

So, what if it's already open?
 

I am not aware of a built-in way of telling if the workbook is already
open by another user or not. You could however only run the code if
the workbook has been opened for editing (e.g. is NOT read-only) as if
someone else already has it open then they would have had to open a
read-only version? In that case you could wrap your code in an "if not
me.readonly then" block.

So, what if it's already open?
 

Thanks Tom.......

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

You can check if the file is open by some other user with this approach:


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

The articles are all basically the same.

If you find the file is open, then don't offer to archive it.

--
Regards,
Tom Ogilvy



"CLR" wrote:

Hi All....
I have the following macro in a program to prompt the users to occasionally
Archive the file. It works fine, however, when someone opens the file, if
another user already has it open, I would like the pop-up not to appear. How
might the below code be modified to accomplish that, please?

Private Sub Workbook_Open()
Dim MyDate
MyDate = Date
Dim LastDate
LastDate = Range("ah37").Value
ans = MsgBox("This LOGBOOK Program has not been Archived since " &
LastDate & ".....Do it now?", vbYesNo)
If ans = vbYes Then
Range("ah37").Select
Selection.Value = MyDate
Application.Run "NewSaveArchive"
Else
End If
End Sub

Thanks,
Vaya con Dios,
Chuck, CABGx3

So, what if it's already open?
 

This one worked fine for me Barry-Jon, many thanks.

Vaya con Dios,
Chuck, CABGx3

"Barry-Jon" wrote:

I am not aware of a built-in way of telling if the workbook is already
open by another user or not. You could however only run the code if
the workbook has been opened for editing (e.g. is NOT read-only) as if
someone else already has it open then they would have had to open a
read-only version? In that case you could wrap your code in an "if not
me.readonly then" block.

So, what if it's already open?
 

I see I misunderstood what your concern is.

--
Regards,
Tom Ogilvy


"CLR" wrote:

Thanks Tom.......

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

You can check if the file is open by some other user with this approach:


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

The articles are all basically the same.

If you find the file is open, then don't offer to archive it.

--
Regards,
Tom Ogilvy



"CLR" wrote:

Hi All....
I have the following macro in a program to prompt the users to occasionally
Archive the file. It works fine, however, when someone opens the file, if
another user already has it open, I would like the pop-up not to appear. How
might the below code be modified to accomplish that, please?

Private Sub Workbook_Open()
Dim MyDate
MyDate = Date
Dim LastDate
LastDate = Range("ah37").Value
ans = MsgBox("This LOGBOOK Program has not been Archived since " &
LastDate & ".....Do it now?", vbYesNo)
If ans = vbYes Then
Range("ah37").Select
Selection.Value = MyDate
Application.Run "NewSaveArchive"
Else
End If
End Sub

Thanks,
Vaya con Dios,
Chuck, CABGx3

So, what if it's already open?
 

No, Tom, you read me correctly. It's just that those references you cited
were much too complicated for me to understand, so I tried the other
suggestion which was NOT actually what I asked for, but indeed did solve this
particular problem.

Thanks again for your time......

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

I see I misunderstood what your concern is.

--
Regards,
Tom Ogilvy


"CLR" wrote:

Thanks Tom.......

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

You can check if the file is open by some other user with this approach:


http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open

http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open

The articles are all basically the same.

If you find the file is open, then don't offer to archive it.

--
Regards,
Tom Ogilvy



"CLR" wrote:

Hi All....
I have the following macro in a program to prompt the users to occasionally
Archive the file. It works fine, however, when someone opens the file, if
another user already has it open, I would like the pop-up not to appear. How
might the below code be modified to accomplish that, please?

Private Sub Workbook_Open()
Dim MyDate
MyDate = Date
Dim LastDate
LastDate = Range("ah37").Value
ans = MsgBox("This LOGBOOK Program has not been Archived since " &
LastDate & ".....Do it now?", vbYesNo)
If ans = vbYes Then
Range("ah37").Select
Selection.Value = MyDate
Application.Run "NewSaveArchive"
Else
End If
End Sub

Thanks,
Vaya con Dios,
Chuck, CABGx3

So, what if it's already open?
 

Vaya,

Re the code examples on those links. If you run this code in the file
you are examining wouldn't it always return that the file is already
open?

Barry-Jon

So, what if it's already open?
 

I dunno Barry-Jon, it's all beyond me. I just used your simple suggestion to
solve my immediate problem and it worked fine. I only wanted a way to have
my start-up prompt ,(encouraging the user to Archive the file) to NOT pop up
if the file was already being used by another user. Your key on the
"read-only" attribute worked fine for that. thanks again.

Vaya con Dios,
Chuck, CABGx3



"Barry-Jon" wrote:

Vaya,

Re the code examples on those links. If you run this code in the file
you are examining wouldn't it always return that the file is already
open?

Barry-Jon

So, what if it's already open?
 

Cool - sorry I meant to direct the question at Tom. Glad my suggestion
helped.

So, what if it's already open?
 

As I said, I misunderstod the problem stated. I thought he wanted to check
if the archived file was in use - not the workbook he had open. The
articles I provided were for that purpose.

--
Regards,
Tom Ogilvy


"Barry-Jon" wrote in message
ups.com...
Vaya,

Re the code examples on those links. If you run this code in the file
you are examining wouldn't it always return that the file is already
open?

Barry-Jon

So, what if it's already open?
 

Tom - thanks - I wasn't being critical I was just checking I understood
what the code was doing. I have added your links to my bookmarks too.