Blocking a Windows Message

[GrantW]

In an application that I am creating, one of the macros looks to see if
another workbook (from which data will be extracted) is Read Only. If it is
read only, control is passed to a msgbox advising the user to try again, then
the Sub ends. By the time the user clicks ok to the msgbox and tries to run
the macro again, the other workbook is usually not Read Only and everything
runs as it should.
However, Windows will send the user a msgbox shortly after, advising that
the other workbook is now free, and ask if they want to view it. Is there any
way to block that Windows advice from being sent?. It is screwing things up.
I appreciate any help you can provide.

 

[Jim Rech]

Windows will send the user a msgbox shortly after

It's Excel. Have a look at the Workbooks.Open method in VBE help,
specifically the Notify argument which controls whether the workbook
available prompt occurs.

--
Jim
| In an application that I am creating, one of the macros looks to see if
| another workbook (from which data will be extracted) is Read Only. If it
is
| read only, control is passed to a msgbox advising the user to try again,
then
| the Sub ends. By the time the user clicks ok to the msgbox and tries to
run
| the macro again, the other workbook is usually not Read Only and
everything
| runs as it should.
| However, Windows will send the user a msgbox shortly after, advising that
| the other workbook is now free, and ask if they want to view it. Is there
any
| way to block that Windows advice from being sent?. It is screwing things
up.
| I appreciate any help you can provide.

 

[GrantW]

Jim, Thanks for that tip.
Being very green at syntax I have tried to code it like this:

Workbooks.Open
("\\Caffeine\a3_personnel_management\NSR\Database\Dbase.xlsx",,,,,,,,,,False)

with false, so that no notification is sent, but I keep getting a compile
error asking for an expected expression.
What am I missing?

 

[Jim Rech]

The only thing that might be wrong are the parens. Only use them if you are
receiving the returned value of the method. For example either:

Dim MyWB as Workbook
Set MyWB = Workbooks.Open("C:\abc.xls")

or

Workbooks.Open "C:\abc.xls"

It looks like you have parens but no "receiver".


--
Jim
| Jim, Thanks for that tip.
| Being very green at syntax I have tried to code it like this:
|
| Workbooks.Open
|
("\\Caffeine\a3_personnel_management\NSR\Database\Dbase.xlsx",,,,,,,,,,False)
|
| with false, so that no notification is sent, but I keep getting a compile
| error asking for an expected expression.
| What am I missing?
|
| "Jim Rech" wrote:
|
| > >>Windows will send the user a msgbox shortly after
| >
| > It's Excel. Have a look at the Workbooks.Open method in VBE help,
| > specifically the Notify argument which controls whether the workbook
| > available prompt occurs.
| >
| > --
| > Jim
| > | > | In an application that I am creating, one of the macros looks to see
if
| > | another workbook (from which data will be extracted) is Read Only. If
it
| > is
| > | read only, control is passed to a msgbox advising the user to try
again,
| > then
| > | the Sub ends. By the time the user clicks ok to the msgbox and tries
to
| > run
| > | the macro again, the other workbook is usually not Read Only and
| > everything
| > | runs as it should.
| > | However, Windows will send the user a msgbox shortly after, advising
that
| > | the other workbook is now free, and ask if they want to view it. Is
there
| > any
| > | way to block that Windows advice from being sent?. It is screwing
things
| > up.
| > | I appreciate any help you can provide.
| >
| >
| >