"You must enter a number in this cell" - ???

[Guest]

Many moons ago I downloaded a template for an invoice from the MS download
area. I customized it to my needs, have no idea what the orignal looked like
at this point. I use it almost daily, but there is one problem with it that I
have finally had enough of and can't figure out how to get rid of.

In the column I have named Qty, the cell format is set to number, no decimal
places, for the cells in that column. If I enter, say "484." - no problem.
However, if I enter "484" (no decimal point after the digits) or "484.5" I
get a pop-up that says "You must enter a number in this cell. Continue?" and
the choices of yes, no, or cancel. I've been in the habit of pressing "yes"
and continuing on my way, but it's really annoying to have this pop-up all
the time if I forget to hit the . or want to enter something other than a
whole number (484.5, for example).

I have tried changing the cell format to number with 1 or 2 decimal places,
but that doesn't help, the pop-up persists. How can I keep the format as
"number" and get rid of this annoying pop-up?

Thanks,

CD

 

[tylagusim]

Sounds like the message is being generated by VBA code and has nothing
to do with the raw cell formatting. To see if there's VBA attached to
your spreadsheet, try "Alt-F11" (or the menu system "Tools / Macros /
Editor") and in the "Project" window, look for a "Modules" section. If
there's anything there, that may well be the source of the message.
Deleting or disabliong a message generated by VBA can be done, but
there's no generic way of describing how. Less likely, given the
description of the message box, is that the message is the result of
data validation on the cell in question. To check that, try "Data /
Validation" on the menu system and see if there's anything there. If
so, you can remove it within that same dialog box.
HTH

/ Tyla /

 

[Gord Dibben]

Select the cell and Data>Validation.

Clear the DV for that cell.


Gord Dibben MS Excel MVP

 

[Guest]

Thankyou both - it was Data-validation criteria making the pop-up.

CD