Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help with a code reference problem - Excel 2002 XP
I have a problem (in fact several) with a workbook. Under certain
conditions (absolutely reproducable) the sheet seems to reference a code block that does not exist - I have a pic of the window that appears here; http://www.jimsthings.com/excel/Fill_Rota.png the workbook that produces the error is here; http://www.jimsthings.com/excel/8_test.xls the sheet affected is the open sheet (Timesheet) - if I click on any of the cells A:44 to A:60 (approx) I get the error window shown in the picture above. Several people have helped by saying that this seems to be at least partially due to the fact that one of my buttons at the top right - 'Get Staff' - 'Rota' and 'Clear All' are operating on 'protected' sheet and so I need to unprotect it before running the code. Unfortunately, none of the people whi have helped are running the same version of excel as me and the other sheet users - we use Excel 2002 SP3 from Office XP, and maybe the things that are working for them are not working correctly here, although, I stress, I am very grateful for the help and do not want to appear ungracious ! There are in fact then 2 problems, one is the functionality that I am unable to build in, the other is that I would like to understand why this nonexistent code block still seems to be referenced. Short background info - the sheet is used by a parent who manages a 'care package' for their son - staff who look after him are rota'd and their pay worked out by the sheet. As they are definately not pc savvy I need to lock down as much of the code and formulae as possible to prevent accidents happening while the sheet is being used - thats why I have protection turned on and only some cells protected. The 'Rota' Button fills the grid on the 'Timesheet' sheet with a months worth of names Rota'd on the 'Rota' sheet (Rota sheet just has a weekly schedule). The 'Get Staff' button assembles a list of all the names appearing in the Rota grid of sheifts on the 'Timesheet' and copies them to cells below the working area in column 'A'. The 'Clear All' button clears all the names, shift hours and pay rates out as well as clearing the list of unique names produced by the 'Get Staff' button. As it stands the sheet is in 'problem' condition - the only way I can clear that is to unprotect the sheet and then 'Clear All'. The problem created by using the 'Get Staff' code - it has been narrowed down to the code that does the filtering to produce the unique list in column 'A' - this list is created by copying all the names data from columns B, C, E, F, H and J to column AP and then filtering it - after which the temp data in column AP is deleted. Any suggestion or help would be most welcome. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help with a code reference problem - Excel 2002 XP
You have a zero height and width button in cell AP50 which uses the macro mentioned in the error. Unprotect your sheet, then try running this code: Code: -------------------- Sub DelButton Sheets("Timesheet").buttons("Button 21").delete End Sub -------------------- and reprotect again. Isis;731787 Wrote: I have a problem (in fact several) with a workbook. Under certain conditions (absolutely reproducable) the sheet seems to reference a code block that does not exist - I have a pic of the window that appears here; http://www.jimsthings.com/excel/Fill_Rota.png the workbook that produces the error is here; http://www.jimsthings.com/excel/8_test.xls the sheet affected is the open sheet (Timesheet) - if I click on any of the cells A:44 to A:60 (approx) I get the error window shown in the picture above. Several people have helped by saying that this seems to be at least partially due to the fact that one of my buttons at the top right - 'Get Staff' - 'Rota' and 'Clear All' are operating on 'protected' sheet and so I need to unprotect it before running the code. Unfortunately, none of the people whi have helped are running the same version of excel as me and the other sheet users - we use Excel 2002 SP3 from Office XP, and maybe the things that are working for them are not working correctly here, although, I stress, I am very grateful for the help and do not want to appear ungracious ! There are in fact then 2 problems, one is the functionality that I am unable to build in, the other is that I would like to understand why this nonexistent code block still seems to be referenced. Short background info - the sheet is used by a parent who manages a 'care package' for their son - staff who look after him are rota'd and their pay worked out by the sheet. As they are definately not pc savvy I need to lock down as much of the code and formulae as possible to prevent accidents happening while the sheet is being used - thats why I have protection turned on and only some cells protected. The 'Rota' Button fills the grid on the 'Timesheet' sheet with a months worth of names Rota'd on the 'Rota' sheet (Rota sheet just has a weekly schedule). The 'Get Staff' button assembles a list of all the names appearing in the Rota grid of sheifts on the 'Timesheet' and copies them to cells below the working area in column 'A'. The 'Clear All' button clears all the names, shift hours and pay rates out as well as clearing the list of unique names produced by the 'Get Staff' button. As it stands the sheet is in 'problem' condition - the only way I can clear that is to unprotect the sheet and then 'Clear All'. The problem created by using the 'Get Staff' code - it has been narrowed down to the code that does the filtering to produce the unique list in column 'A' - this list is created by copying all the names data from columns B, C, E, F, H and J to column AP and then filtering it - after which the temp data in column AP is deleted. Any suggestion or help would be most welcome. Thanks -- aflatoon Regards, A. ------------------------------------------------------------------------ aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=205117 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help with a code reference problem - Excel 2002 XP
aflatoon wrote in
: Sheets("Timesheet").buttons("Button 21").delete aflatoon, thanks very much for taking the time to find this. If I may ask a couple of questions; Can I just enlarge this button so I can see it ? I cannot seem to find the code syntax to do that. How did you find the button ? Why doesn't the code on the button how up anywhere in teh code or macro editors ? Trying to learn something here :-) Thanks for the help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help with a code reference problem - Excel 2002 XP
You should be able to use syntax like: Code: -------------------- With Sheets("Timesheet").buttons("Button 21") .Height = 20 .Width = 40 End With -------------------- for example. I found it purely because I was looking for it! It's the only thing I know of that would cause the symptoms you were describing, so I wrote some code to output the names and locations of every button on the sheet. The code does not actually exist any more. The buttons had macros assigned to them, but the macros have since been removed. It's an occasional oddity that can occur with buttons and protected sheets - usually seems to happen due to deleting cells that the button was located in. Isis;731903 Wrote: aflatoon wrote in : Sheets("Timesheet").buttons("Button 21").delete aflatoon, thanks very much for taking the time to find this. If I may ask a couple of questions; Can I just enlarge this button so I can see it ? I cannot seem to find the code syntax to do that. How did you find the button ? Why doesn't the code on the button how up anywhere in teh code or macro editors ? Trying to learn something here :-) Thanks for the help -- aflatoon Regards, A. ------------------------------------------------------------------------ aflatoon's Profile: http://www.thecodecage.com/forumz/member.php?u=1501 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=205117 http://www.thecodecage.com/forumz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help with a code reference problem - Excel 2002 XP
aflatoon wrote in
: You should be able to use syntax like: Code: -------------------- With Sheets("Timesheet").buttons("Button 21") .Height = 20 .Width = 40 End With -------------------- for example. I found it purely because I was looking for it! It's the only thing I know of that would cause the symptoms you were describing, so I wrote some code to output the names and locations of every button on the sheet. The code does not actually exist any more. The buttons had macros assigned to them, but the macros have since been removed. It's an occasional oddity that can occur with buttons and protected sheets - usually seems to happen due to deleting cells that the button was located in. Isis;731903 Wrote: aflatoon wrote in : Sheets("Timesheet").buttons("Button 21").delete aflatoon, thanks very much for taking the time to find this. If I may ask a couple of questions; Can I just enlarge this button so I can see it ? I cannot seem to find the code syntax to do that. How did you find the button ? Why doesn't the code on the button how up anywhere in teh code or macro editors ? Trying to learn something here :-) Thanks for the help aflatoon, Thanks very much for spending the time to help - I appreciate it. The Protect/Unprotect thing is a lot more complicated that one might like but I will have to get to grips with it if the worksheet is gong to be an use to the poeple who need to use it - one step nearer ! Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Non existent code reference problem | Excel Programming | |||
Excel 2002: How to get the first and last reference in a column ? | Excel Discussion (Misc queries) | |||
Excel 2002 has encountered a problem and needs to close /Code Clea | Excel Programming | |||
Problem with VBA code written in Excel 2002 working in Office 2003 | Excel Programming | |||
Code Reference Problem | Excel Programming |