Well . . . . . Cleo Peterson, here I am. I thought I had it working. I
thought it was working when the workbook was shared. But no, I must have had
the one sheet Unprotected and not realized it.
So here is the alternate route I was trying: same workbook as before but
I created a single sheet workbook that linked to the original workbook page
I was needing Protected. The single sheet can stay Protected and one person
can update the main workbook to which the single sheet links. It seemed like
a good idea and "looked good on paper" but the execution is lacking. I
somewhat understand why the following happens but I don't completely
understand and need a solution.
First a little background on the idea behind the workbook: I have a team
in different offices around a building. I keep track of cars driving around
the building and want to let everyone on my team know what each make, model,
color and license plate is for all the cars I see. I want them to keep an
eye out for the same cars so I need to update them quickly on what I see. So
I have a source workbook I will update throughout the day. It will be easier
for them if all the makes are together in the sheet. When I enter each car's
specs I alpha sort the sheet by make. Each team member has access to the
linked single sheet but not the source workbook. Throughout the day they
leave the linked sheet open on their computer so they can quickly get the
latest info but I want it Protected so they don't edit the car info.
OK, there's the idea. The execution is this: The files are set to update
automatically. I have the source workbook and the linked sheet in the same
folder on a shared network drive. I have both open on my computer. I update
the latest car info, sort and view the linked single sheet - the latest car
is there in the correct alpha order. I go to my co-workers computer and open
the linked sheet for the first time. The latest car is there but not
sorted - it is at the bottom of the list where it is placed after I
initially enter the car. If I leave the their sheet open when I update the
next car it does not appear on the sheet, at least not for some great length
of time which I have yet to determine. I have found a lot of info saying
this can happen with large files with lots of cells to update and
particularly with lookup references. The spreadsheet is 250 cells long by 6
wide with no lookup references.
So, 1) Why does the car stay at the bottom of the list even though the
source list is sorted and 2) What can be done to make the update quick. And,
3) Knowing what I am trying to do is there a better solution whether with
Excel or Access? This is all on a company network so there is no option to
install additional applications.
I hope everyone is still awake who started reading this thinking they
could help. Thanks in advance
.
Dave Peterson said:
Oh, oh.
That works nicely on workbooks that aren't shared. Shared workbooks are
troublesome when it comes to worksheet protection.
(As Ahnold may have said: You'll be baaack.)
J said:
I found the workaround. . . and is from a post you, Dave, participated in
from Exceltip.com. As was suggested thereI entered an Auto_Open sub that
uses userinterfaceonly:=True. The cells will update but they remain
Protected. Although I still need to test it on XL97 tomorrow it should work
with little more effort.
So cool
I think that the help is correct in this case.
Look under "Features that are unavailable in shared workbooks"
You'll find this:
Protect or unprotect worksheets or the workbook
Existing protection remains in effect.
J Marro wrote:
I found resources to say this can happen but nothing that really
addresses
how to correct it. At least none that directly apply or that I can
manage to
modify to make it work.
The following macro works fine until I Share the workbook and select
to
"Allow changes by more than one user". At that point I get the error :
Run-time error '1004'
Unprotect method of Worksheet class failed
What do I need to correct so this macro works on the Shared workbook?
Thanks
Sub AMupdate()
Application.ScreenUpdating = False
Sheets("Broker List").Select
ActiveSheet.Unprotect
Sheets("Paste Attachmate").Select
Range("E28").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[-21]C[1],"" "",R[-21]C[2],"" "",R[-21]C[3],""
"",R[-21]C[4],"" "",R[-21]C[5],"" "",R[-21]C[6])"
Selection.Copy
Sheets("Broker List").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Sheets("Paste Attachmate").Select
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Range("A1:B1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Sheets("Paste Attachmate").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A1").Select
