Protectview and open workbook

[sjors.hoevenaars]

I'm using a file "count.xlsm" in which our inventory employee registers his counts. I created a procedure that filters all results on difference and reasoncodes, and selects the resulting rows of the table. That all works properly.

There is another workbook that is called "Report.xlsm" and contains two sheets: "Summary2012" and "Report". The second sheet contains a selection of the data on the first sheet and is protected except for the columns in which users have to do input. The first sheet is not protected.
The selection made in the first workbook should be pasted to the (not protected) summary2012 sheet of the Report workbook.

The code I use for that is:

Please Login or Register  to view this content.

and then follows the code to direct to the first empty row as the paste destination.
If I run this macro, it results in error 1004 which refers to ProtectedView workbooks. How can I avoid that the workbook opens in protected view?

What code do I need to find out whether the workbook is already open, so I can step over the workbook.open command, since that will also result in an error.


Sjors

[Winon]

Hello sjors hoevenaars,

If the workbook is protected, then you will have to add code to unprotect the workbook when opening it, or likewise if it is the Sheet which is protected. Something like:

[QUOTE] Sheet1.Unprotect ("Password")[QUOTE]

and then before closing the workbook you just change the code to

Sheet1.Protect ("Password")

[snb]

Please Login or Register  to view this content.

NB Vermijd 'Select' en 'Activate' in VBA

[Winon]

@ snb

Hello snb,

Am I barking up the wrong tree assuming it has to do with some Protection issue? If some kind of protection is in place, surely your code wont bypass the Protection?

Just asking

[sjors.hoevenaars]

Hi,

it is indeed not a password protected workbook. One of the sheets is protected but that is not the sheet to which I want to copy the data.

I will try the code given by snb. I guess that will at least solve the problem of trying to open an already open workbook.

Just leaves the protectedview issue...

[Winon]

Hello sjors,

I am so sorry about that, reading your post again, I realised that I have missed:

pasted to the (not protected)

snb is a great guy, and is usually spot on with his assesment of what code you need to help solve a problem!


@snb

To you as well, snb. I trust you won't keep anything against me. Sorry about that!

Ek was moeg, en het dit nie raak gesien nie.

Baie Dankie julle!

Groete

[sjors.hoevenaars]

Unfortunately the code does not excactly work.
What i want is to check whether the destination workbook is already open.
If that is the case, I want a messagebox to say to first close the destination workbook, and exit the sub.
If the workbook is not open, the code opens the workbook and finds the specifies range to paste (this part works fine, except when the workbook is already open).