Macros don't work in shared workbook?

[StargateFanFromWork]

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]

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.

 

[StargateFanFromWork]

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.

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! D

 

[Dave Peterson]

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????

 

[StargateFanFromWork]

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????

<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. D

 

[Dave Peterson]

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.

 

[StargateFanFromWork]

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. D

 

[Dave Peterson]

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
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. D

 

[StargateFanFromWork]

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.

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! D