Prevent data entry of text beginning with "-"

  • G'day everyone,


    I am a very novice VBA programmer and need some help on a spreadsheet I am trying to produce.


    What I am hoping to achieve, is to try to limit text entries in a cell to prevent entries beginning with a '-' sign. ie "12345 cars" is ok, but "-12345 cars" is not okay. The "-" sign is the key trigger which will prevent data entry.


    If the user does try to enter data beginning with a minus sign, the program shouldn't provide any warnings, alerts or pop up boxes, it should just prevent the data entry.


    As this is workbook specific in my application, I'm assuming VBA is the best way of achieving this and not changing program settings.


    Does anyone know if this is even remotely possible?


    Cheers,


    Jeremy

  • "try to enter data beginning with a minus sign ... it should just prevent the data entry"


    VBA cannot monitor input while a cell is being edited, only after the edit has been committed by pressing Enter or the user moving the cursor to a different cell so there is no way to prevent entry in the first place.


    Best you can do is allow entry and then check but it seems very restrictive to check for a '-', what if the user entered 'x5 cars' or '#$&*$ cars'??

  • One could do this by creating a custom event that uses Windows API calls, specifically to PeekMessage, PostMessage, and FindWindow. Do you know how to program in a Windows object-oriented language, e.g., C#?

  • You can do this using Data Validation. If the cell is A1, select the cell and choose Data Validation. Then choose Custom for Allow: and enter this for Formula:


    =NOT(LEFT(A1)="-")


    On the Error Alert tab, type in the message you want. Perhaps something like "Negative Not Allowed" for Title: and "Value must not start with dash." for Error message:.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!