Still need help with a code reference problem - Excel 2002 XP

[Isis]

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]

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.


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

 

[Isis]

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]

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
know of that would cause the symptoms you were describing, so I wrot
some code to output the names and locations of every button on th
sheet.

The code does not actually exist any more. The buttons had macro
assigned to them, but the macros have since been removed. It's a
occasional oddity that can occur with buttons and protected sheets
usually seems to happen due to deleting cells that the button wa
located in.

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 o macro
editors ?

Trying to learn something here

Thanks for the help

 

[Isis]

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.

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