Non existent code reference problem

[Isis]

I have a sheet that seems to get 'stuck' calling a block of non existent
code - it happens after I clear the sheet then click any of a number of
cells.

It has something to do with protection as it does not happen if I unprotect
the sheet - obviously noone is going to be able to pinpoint this from a
short description !

How can I find the reference to the the non existent code block in my code
- I have done a 'find' within the code but I am not seeing it ?

Any other ideas would be most welcome.

Thanks

 

[Per Jessen]

Hi

As always post the code for comments.

Well, as the code run when the sheet is unprotected, I guess that your code
is trying to trying to change one or more cells in the protected sheet,
which it can not.

The solution is to unprotect the sheet by code, then change cells and
protect it again

sub MyMacro
Worksheets("Sheet1").unprotect Password:="JustMe"

'Your Code

Worksheets("Sheet1").Protect Password:="JustMe")
End Sun

Regards,
Per

 

[Isis]

Hi

As always post the code for comments.

Well, as the code run when the sheet is unprotected, I guess that your
code is trying to trying to change one or more cells in the protected
sheet, which it can not.

The solution is to unprotect the sheet by code, then change cells and
protect it again

sub MyMacro
Worksheets("Sheet1").unprotect Password:="JustMe"

'Your Code

Worksheets("Sheet1").Protect Password:="JustMe")
End Sun

Regards,
Per

Per, thanks very much for the reply - I don't think I can post the code,
there is a lot of it, but I could post the entire workbook if that is
allowed ?

I realised that the problem is the locked/unlocked status of the cells,
but the block of code being called does not exist as far as I can tell -
the name that comes up does not exist that to to say.

Anyhow, if it's ok to post the workbook I will ? Should I zip it
beforehand ?

Thanks

 

[JLGWhiz]

I do not believe this site allows an entire workbook to be posted. You
would have to save the workbook to a sharepoint sever and then reference the
file in a post on this site. But you should be able to open the VBE and
copy the portion of the macro that you are having trouble with, and then
copy and paste that to this this site. Perhaps you are not familiar with
how that works.

 

[Isis]

I do not believe this site allows an entire workbook to be posted.
You would have to save the workbook to a sharepoint sever and then
reference the file in a post on this site. But you should be able to
open the VBE and copy the portion of the macro that you are having
trouble with, and then copy and paste that to this this site. Perhaps
you are not familiar with how that works.

Hi JLGWhiz - I can copy the code from the sheet, no problem. The problem is
that I don't think it is this code that is the problem - the error I get -
in a particular circumstance is referencing a macro that does not exist. I
have been through all the code I have written, but cannot find a reference
to this 'nonexistent' 'macro' - the nonexistent macro is apparently
Fill_Rota, but I only have a Fill_Rota_New - Of course I could have missed
it when going though the code, and YES it is true, I did once have a Sub
Fill_Rota() section, but it was renamed Fill_Rota_New and I just can't see
anything referencing Fill_Rota now.

I will put the workbook up in the 'failing' condition that it gets stick in
- clicking on the A50 cell on the open worksheet will demonstrate the error
I get.

Any help is very much appreciated

Sheet can be downloaded here;

http://www.jimsthings.com/excel/Timesheet T7a.xls

Thanks

 

[Isis]

I do not believe this site allows an entire workbook to be posted.
You would have to save the workbook to a sharepoint sever and then
reference the file in a post on this site. But you should be able to
open the VBE and copy the portion of the macro that you are having
trouble with, and then copy and paste that to this this site. Perhaps
you are not familiar with how that works.

I stupidly forgot to say that the error can be 'reset' by turning off
protection for the first sheet.

It happens if you use the buttons top left in the order;

'Rota' then 'Get Staff' with the sheet protected.

Thanks

 

[EricG]

This code does not work for me (in 2007) due to the fact that the sheet is
protected:

Sheet4.Range("AP1:AP" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet4.Range("A" & 48), _
Unique:=True

If I wrap it in an Unprotect/Protect set, as below, it works fine. Try this
before continuing to investigate the mystery of the missing code.

Sheet4.Unprotect

Sheet4.Range("AP1:AP" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet4.Range("A" & 48), _
Unique:=True

Sheet4.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows _
:=True, AllowSorting:=True, AllowFiltering:=True

HTH,

Eric

 

[Per Jessen]

Hi

I tested your workbook, and the only error I could find was the advanced
filter function in the 'Sub Get_Staff_List()'

Although you have allowed Advanced filter on the protected sheet, it fails
because it also try to copy the cell formatting (colors), which is not
allowed.

While you get the error click Debug to see which line is causing the error
(highlighted in yellow).

Using a Unprotect/Protect statement solved it:

Sheet4.Unprotect
Sheet4.Range("AP1:AP" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet4.Range("A" & 48), _
Unique:=True
Sheet4.Protect

Regards,
Per

 

[Isis]

This code does not work for me (in 2007) due to the fact that the
sheet is protected:

Sheet4.Range("AP1:AP" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet4.Range("A" & 48), _
Unique:=True

If I wrap it in an Unprotect/Protect set, as below, it works fine.
Try this before continuing to investigate the mystery of the missing
code.

Sheet4.Unprotect

Sheet4.Range("AP1:AP" & LastRowNewData).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Sheet4.Range("A" & 48), _
Unique:=True

Sheet4.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowDeletingColumns:=True,
AllowDeletingRows _
:=True, AllowSorting:=True, AllowFiltering:=True

HTH,

Eric

Per and Eric, thanks very much for checking this out for me - I will
insert the code to protect and unprotect the sheet and see what happens
tomorrow - still don't understand the reference to the nonexistent code
but mayber that is unimportant.

Regards