Macro fails after sharing workbook

[Rabbitoh]

G'Day All, I need some help from the masters please.

Explanation:

Worksheet "Sheet1" contains a listing of multiple addresses in columns containing "City" and "Street". This worksheet is protected to prevent users from changing the format,
layout, etc; plus to protect command buttons used for navigation and initiating other macros.

Worksheet "Sheet2" contains only temporary data (sourced from Sheet1) that is used as a lookup from a dropdown box.

This macro performs the following:

Sheet2 - cleared of all previous data
Sheet1 - unprotect sheet to allow for next actions to take place
Sheet1 - filters removed to ensure all data is displayed
Sheet1 - sort data to incorporate any new entries added since last sort
Sheet1 - because there are duplicate "cities" in the City column, the entries are filtered into unique values (to produce only one instance of each City), then copied to Sheet2.
This filtered list now on Sheet2 is later referenced by a dropdown box, with the output being displayed on a Userform.
Sheet1 - autofilter is restored and is protection turned back on

Please Login or Register  to view this content.

The macro works perfectly until the workbook is SHARED. Then it crashes due to the following:

Sheet PROTECTION cannot be turned OFF and ON when in shared state
The FILTERING to unique values cannot be performed when in shared state

Question: Is there another way to do these actions so that everything works when the workbook is in a shared state?

[TMS]

Short story:no, not really.

Limitations of shared workbooks:

https://support.office.com/en-us/art...ID=HP010342985

[Rabbitoh]

huumm, not good. I guess I can drop the sheet protection from the macro. Is there a way to at least lock down the command buttons on an unprotected sheet so that they do not get deleted or relocated by accident?

[TMS]

I've asked if anyone else has any ideas or suggestions but I wouldn't hold your breath.

http://www.excelforum.com/showthread...=1#post4572230

[Richard Buttrey]

huumm, not good. I guess I can drop the sheet protection from the macro. Is there a way to at least lock down the command buttons on an unprotected sheet so that they do not get deleted or relocated by accident?

Are individual users restricted to their own areas/sheets within the workbook or are they allowed to edit common areas?

[xlnitwit]

Hi,

Have you considered using a menu system in the Ribbon rather than buttons on worksheets for navigation? Or perhaps a floating userform?

I should point out, just in case you are unaware, that most of the prominent Excel folk seem to regard shared workbooks as a really bad idea. Are you forced to use them?

[Rabbitoh]

Common areas for everyone

[Rabbitoh]

An idea then, I can for-go the sheet protection, then could the following be a solution to the unique list problem:

Create the unique list of Cities before sharing the workbook. Then scrap that part of the macro and instead use a COMPARE and COPY routine under the shared state to compare the contents of the "City" list in Sheet1, with the filtered unique list of Cities in Sheet2. If any new ones are found in the Sheet1 list then they get appended to the list is Sheet2. The Sheet2 list can then be sorted to produce the orderly list of Cities to display in the drop-down box. If so, what CODE would I need to achieve this??

There doesn't seem to be anything on the Microsoft share prohibition list that says I can't do this!

[Rabbitoh]

Ideally the share would be required to make it multi-user editable without having to wait until another user closes it.

[Arkadi]

When many users need to be able to access and update centralized data I've always preferred using a database (typically access in our case), while using a read-only spreadsheet that is only an interface, I load the data from the database on open, and data that needs adding or updating is done via macros either on buttons or userform objects/actions.

[Rabbitoh]

I believe I have solved the problem of the unique value portion of my code that doesn't work in the SHARE state by adapting
the "Compare" code provided elsewhere in the forum by Forum Contributor "cantosh" for user ncarrotine on 03-16-2016, 11:28 AM, under
the title "macro to compare and clear cells in two columns"

By first copying the non-static (changing) data on Sheet1, to the rows on the SAME COLUMN well BELOW the static data on Sheet2, I then run the Compare to separate out all the entries that do not appear on the Sheet2 list. I then SORT the entire range so that the new entries are incorporated and
alphabetised into the one list. i have tested it in the SHARE state and it works!

Here it is:

Please Login or Register  to view this content.

Thanks to all who assisted with this problem. In respect to the UNPROTECT-PROTECT issue in the SHARE state, I will leave that for now until I have either inspiration to find a way around it, or frustration and just remove protection all together.

[Rabbitoh]

An update on both issues rasied in this query:

Firstly, I have found that my Command Buttons remain locked down in the SHARE state even without sheet protection being on because all macros and objects get locked anyway when SHARE is turned on.

Secondly, the solution I posted above for creating a unique list of "Cities" worked fine for a small list but missed the mark when I tried with a large list. So to ensure Forum integrity of solutions, I am posting my second solution which works with large lists, and which also works when the workbook is in a SHARE state. This second solution not only also allows for new cities to be added, but also for cities no longer required to be removed, something I didn't consider with the previous code.

Step 1 is to first create a baseline list of Cities on Sheet2 (from cell A2 down) from which the following code will compare with the active list on Sheet1, then update going forward.

Please Login or Register  to view this content.