Protected 2007 Workbook violated by Iphone 6? Safe Alternatives?

[Lunaire]

To give you a bit of background - part of my job is to create 300+ workbooks to be sent out to Companies to be completed and returned. From this I then create VBA coding to pull them into one master document. The documents themselves are created very simply, using a hidden tab containing the data of all 300 Companies and lookups to bring the data through to the sheet to be completed. The sheet is then password protected with specific cells allowed to be accessed, and the Workbook protected using another password.

The documents are created in Excel 2007 and are xlsx format.

Now, we have been using this method for a number of years, but in the last week, one Company has put in a formal complaint saying that they opened their document on the Excel app on their Iphone 6 which completed disabled all the protection settings, leaving them seeing all 300 Companies data.

Does anyone know anything about Iphone operating systems or the apps themselves - is this right? Surely this is a security threat between Microsoft and Apple?

On the basis that I can't now create the documents simply with the lookups, is there a simple way of creating these documents that leave no trace of the source data? Maybe using VBA? I am fairly new to VBA but can follow simple code and adapt it to suit. How safe is using lookups as I have been doing, but keeping the source data in a separate document? My experience of this tends to be error messages on opening the documents about links being unable to update etc.

I am panicking slightly and paranoid about the data being completely secure (if there is such a thing!).

 

[RandyMorris]

I would recommend bumping this on Monday or Tuesday when people are back in the office checking as I cannot answer the overarching question, but perhaps I can offer a solution to your issue.

I have a lookup that pulls from a master tab with a lot of different department's and their data. I have to get these to the Department leads via email. My email macro takes only Department X's data (a couple of tabs) and emails it to Department X's lead. It continues until all departments get their own data, with no other data but theirs in the the emailed worksheets. Basically, it just emails the tabs I tell it to, and I still have the master file with every Department's data. I would recommend you do something like that. I should note mine is done with Outlook, but a quick Google search suggests it can be used with other email providers as well.

To answer your questions in the 5th paragraph, yes & safe by your definition. Use your look ups, copy and paste the entire tab, then copy & paste special values, and you just have that companies data, while also retaining your look up tab. Then rinse repeat for each company (obviously VBA would be the most efficient means of accomplishing this). In my above suggestion, I have created VBA that does everything from create the worksheet, to emailing out each tab to their respective owners, without giving other departments data to which they should not have access.

 

[Lunaire]

Many thanks for the reply, your solution sounds exactly what I need to do from now on.