So, what if it's already open?

[CLR]

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

[Tom Ogilvy]

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
>
>

[Barry-Jon]

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.

[CLR]

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
> >
> >

[CLR]

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.
>
>

[Tom Ogilvy]

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
> > >
> > >

[CLR]

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
> > > >
> > > >

[Barry-Jon]

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

[CLR]

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
>
>

[Barry-Jon]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Barry-Jon]

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.