Inconsistent results with function call....?

[Dominic]

With the help of the posts at this site, I've managed to achieve a fairly
crude application for someone here in the office.

However, I am currently stuck. I hope someone will take the time to help me.

I have a sheet with data in rows across multiple columns (18). I have a
button on the sheet to add another sheet. This procedure asks for the user's
input for a sheet name. The procedure then copies the current sheet to a new
sheet with the user's input as its name. It then deletes all the data in a
specified range of cells (all data cells). It then sets the
interior.colorindex to "None". This is because there is a workbook_change
event that changes the interior.colorindex to "Blue".

All this works fine. However, I have on the sheet code (from Bob Phillips)
to count based on font.colorindex. This code works great. The problem I am
having (sorry to be so long winded) is that when the new sheet is created
(copied) and the cells are deleted (set to ""), the font.colorindex remains
the same. I have a button to "Calculate Colors" which searches for blank ("")
cells and set the font.colorindex to 1. This works perfect as a button.
However, trying to call this procedure at the end of the "MakeSheet"
procedure isn't working. I've tried everything I can think of. I tried to
call the "Calculate Colors" procedure at the end of the "MakeSheet" procedure
with no success. I have also copied and pasted the code from "Calculate
Colors" to the end of "MakeSheet" procedure with no success either. If I
simply click this button on the new sheet, it acheives the desired results???

I can provide my code if necessary. Does anyone have any ideas on what I am
missing? Thank you very much.

[goober]

If the sub name is set for a command button i.e. Name_Click() then you may need to copy it into an new sub without the _click. i.e. Name(). That is the only way I have ever been able to get it to work.

Hope it helps.

[Dominic]

Goober,

Thanks for the reply. I tried your suggestion but with no luck. Then I
noticed I was a moron and figured out what was wrong. FYI I called the button
("_click") procedure with no problems.

Thanks again.

"goober" wrote:

>
> If the sub name is set for a command button i.e. Name_Click() then you
> may need to copy it into an new sub without the _click. i.e. Name().
> That is the only way I have ever been able to get it to work.
>
> Hope it helps.
>
>
> --
> goober
> ------------------------------------------------------------------------
> goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
> View this thread: http://www.excelforum.com/showthread...hreadid=486457
>
>