Input cells restriction

[Airborne]

Hello, I have a sheet where we put numbers in cells..... duh. That's normal . But the values are always negative numbers. Sometimes we forget the "-". So i.s.o. -12,50 bij mistake we put in 12,50. How can I prevent positive input in the cell?


Thanks and regards.

[Ken Puls]

Hey again!

Try going to the Tools Menu, and choose Data Validation. You'll want to set it to allow Decimals (or whole numbers) Less than 0.

Just be aware that if you try to copy values into that range using a macro later, that the macro does not trigger validation rules. For user entry, though, it's awesome. You can click on the tabs to set custom messages, and wether you want warnings (so people can enter values if they really want) or outright denial.

Cheers,

[Airborne]

Thanks again Ken. Just an option in Excel! I should have known this by now . It sure is a handy tool.

But.... . A macro that will make all the values in a range, negative, would also be handy. [VBA]Sheets ("Test").Range ("A1:G15)..... [/VBA] and then....I'm working on that

Thanks and regards.

[Ken Puls]

Hi,

ASAP Utilities has a tool to do this, but if you don't want to use theirs, try this out. Just select a range, then run it:

Sub MakeNegative()
Dim cl As Range
For Each cl In Selection
cl.Value = -Abs(cl.Value)
Next cl
End Sub

HTH,

[Airborne]

As usual.... . Works!


Thanks Ken and regards.



p.s ASAP utilities??

[Ken Puls]

Oh man!


Airborne, huge apologies! I just realized that I never got back to answer your question here!

ASAP Utilities (found here) is a free add-in that you can download with a bunch of useful Excel tools. I generally prefer to build my own versions, as I really want to know how they work, not just let it happen, but sometimes I don't have the time or expertise to do that. ASAP works pretty well.

Actually, the only thing that I really dislike about it is that it will expire, and you are forced to download an updated version. Even so, it's always free, so it's only a time complaint.

It might be something you want to check out...

Cheers, (and sorry again for the delay!)

[Airborne]

Hi Ken.

No problem. I've checked the site and downloaded the tool. It sure is handy but I feel the same way, I want to know how it works.

Regards