Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
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" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
"Per Jessen" wrote in
: 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" skrev i meddelelsen ... 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, 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
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" wrote in message ... "Per Jessen" wrote in : 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" skrev i meddelelsen ... 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, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
"JLGWhiz" wrote in
: 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" wrote in message ... "Per Jessen" wrote in : 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" skrev i meddelelsen ... 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, 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
"JLGWhiz" wrote in
: 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" wrote in message ... "Per Jessen" wrote in : 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" skrev i meddelelsen ... 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, 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
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 "Isis" wrote: 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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non existent code reference problem
?B?RXJpY0c=?= wrote in
: 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 "Isis" wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with "Application.Goto Reference" code line | Excel Programming | |||
Link to non-existent files? | Excel Discussion (Misc queries) | |||
Formula for Reading A Non-Existent Reference | Excel Worksheet Functions | |||
Code Reference Problem | Excel Programming | |||
Need (perhaps non-existent) Formula Help | Excel Programming |