Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / September 2007

Tip: Looking for answers? Try searching our database.

Macros don't work in shared workbook?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
StargateFanFromWork - 11 Sep 2007 15:03 GMT
I could have sworn they did in others that I've used where the workbook is
shared.  Is there anything I should be looking at?

I fully understand that we can't view or edit them when they're shared, but
the macros should work, shouldn't they?  It would defeat the purpose of
having them, I would have thought.  So obviously, I'm missing something.
But what ... that's the question <g>.
Dave Peterson - 11 Sep 2007 15:35 GMT
In general, macros should work in shared workbook--if the user allows macros to
run(!).

But maybe your macros are trying to do illegal stuff in that shared workbook and
you're masking the error--so it looks like they're not working?

on error resume next
activesheet.unprotect password:="hi"

would look like it didn't run.

> I could have sworn they did in others that I've used where the workbook is
> shared.  Is there anything I should be looking at?
[quoted text clipped - 3 lines]
> having them, I would have thought.  So obviously, I'm missing something.
> But what ... that's the question <g>.

Signature

Dave Peterson

StargateFanFromWork - 11 Sep 2007 17:00 GMT
> In general, macros should work in shared workbook--if the user allows
> macros to
[quoted text clipped - 18 lines]
>> having them, I would have thought.  So obviously, I'm missing something.
>> But what ... that's the question <g>.

Hmm, I understand.  And looking over this again, I may be doing something
"illegal" in terms of the macro code.  Here are all the macros in the sheet:
*************************************************************
Sub AddNEWentry_NO_SORT()
'
   Cells(ActiveCell.Row, "B").Select     'this extra row for better
worksheet view
   Range("A1").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(1, 0).Select
End Sub
Sub AddNEWentry_SortINVOICElog()
'
ActiveSheet.Unprotect             'place at the beginning of the code
   Application.Goto Reference:="R1C1"
'----------------------------------------------------------------------------------
' SORTING section of code:

   Selection.SORT Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("B2")
_
       , Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending,
Header:= _
       xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

   Selection.SORT Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess,
_
       OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'----------------------------------------------------------------------------------
   Cells(ActiveCell.Row, "B").Select     'this extra row for better
worksheet view
   Range("A1").Select
   Selection.End(xlDown).Select
   ActiveCell.Offset(1, 0).Select
ActiveSheet.Protect    'place at the end of the code
End Sub
Sub GoToBeginningOfRow()
ActiveSheet.Unprotect             'place at the beginning of the code
   Cells(ActiveCell.Row, "B").Select     'this extra row for better
worksheet view
   Cells(ActiveCell.Row, "A").Select
' Re-protects sheet in case anything unprotects it, yet allows vb
functioning to remain.
   With ActiveSheet
      .Protect UserInterfaceOnly:=True
   End With
ActiveSheet.Protect    'place at the end of the code
End Sub
*************************************************************

I get the error:

"Run-time error '1004':
Unprotect method of Worksheet class failed."

I hadn't seen the reference to "unprotect" earlier.  I guess XL doesn't like
to unprotect shared workbooks?  But if we don't unprotect, the sort won't
happen and users can really screw up the workbook, too.

Anyway, are there any solutions?

Thanks!  :oD
Dave Peterson - 11 Sep 2007 17:08 GMT
You can't change the protection of a worksheet in a shared workbook.

You're going to have to make a choice of either leaving the worksheet
unprotected or not sharing the workbook--or use some sort of workaround?

Copy the values to another worksheet (unprotected)
and allow the user to sort to their heart's content????

> > In general, macros should work in shared workbook--if the user allows
> > macros to
[quoted text clipped - 85 lines]
>
> Thanks!  :oD

Signature

Dave Peterson

StargateFanFromWork - 11 Sep 2007 18:17 GMT
> You can't change the protection of a worksheet in a shared workbook.
>
[quoted text clipped - 3 lines]
> Copy the values to another worksheet (unprotected)
> and allow the user to sort to their heart's content????

<sigh>  You know, I just knew that was going to be the case ... <g>

Well, I'm going to have to play dumb.  Since I'm responsible for the file,
I'll just leave the sharing off.  It's not like they need to get into the
file a lot while I'm in it.  I'll have to take my chances with that as it's
more important for me to keep the integrity of the file, etc., and I had a
scare yesterday when I thought I'd deleted a column by mistake.  After that
scare, will just have to leave the workbook unshared and try to scramble
around whenever there's an issue with this.  And temp or no temp, I started
making backups, too, which I only start doing after I've been a place for a
while.  I've just put the copies on my desktop.

Thanks.  Appreciate knowing this.

Cheers.  :oD

>> > In general, macros should work in shared workbook--if the user allows
>> > macros to
[quoted text clipped - 93 lines]
>>
>> Thanks!  :oD
Dave Peterson - 11 Sep 2007 20:25 GMT
If you're making backup copies, you may want to put them on a network drive
that's backed up (to tape??) each night.

It might be safer than copies on your desktop.

> > You can't change the protection of a worksheet in a shared workbook.
> >
[quoted text clipped - 121 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

StargateFanFromWork - 12 Sep 2007 14:44 GMT
> If you're making backup copies, you may want to put them on a network
> drive
> that's backed up (to tape??) each night.
>
> It might be safer than copies on your desktop.

Yes, thanks <g>.  Forgot to mention that they're already doing that.  I'd
made significant changes to the sheet before believing I'd lost the column
for good.  The backup would have been better than nothing, of course, but
this way, by making regular backups every couple of hours, I'll feel safer.

The thing is that here's another situation that always has me leery.
They're sharing a vital file amongs several people that logs all the
invoices they receive throughout each day, every day.  That frightens me as
Excel is such a great app but so easy to screw up re this type of thing.
They need a database for this.  However, I'm just a temp and I never say
anything, I just go about my business and take all the precautions I feel
are necessary when I'm put in charge of a file such as this.  And that's
what I've done.  So I feel doubly secure now.  The backup they do at night
is supplemented by my backup that I do whenever I've entered a significant
amount of information.

Thanks.  Cheers.  :oD

>> > You can't change the protection of a worksheet in a shared workbook.
>> >
[quoted text clipped - 133 lines]
>> >
>> > Dave Peterson
Dave Peterson - 12 Sep 2007 17:55 GMT
If you don't feel comfortable telling your bosses that the data would be more
secure in a database, maybe you can say something when your contract ends.

You might find that it falls on deaf ears, but you may find that someone agrees
with you, too.

> > If you're making backup copies, you may want to put them on a network
> > drive
[quoted text clipped - 161 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

StargateFanFromWork - 12 Sep 2007 18:07 GMT
> If you don't feel comfortable telling your bosses that the data would be
> more
[quoted text clipped - 3 lines]
> agrees
> with you, too.

You're right.  And perhaps I will do that, indeed at the contract's end.
Too many times I see Excel being used as a database.  It's such a wonderful
program but that's not its function esp. when you go beyond a certain number
of items.  Ah well ... I guess they've been lucky so far.  At least I also
locked the workbook by protecting it, so that should help somewhat.

Cheers!  :oD

>> > If you're making backup copies, you may want to put them on a network
>> > drive
[quoted text clipped - 180 lines]
>> >
>> > Dave Peterson
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.