-1

I have an Excel 2010 sheet that content editors use for rewriting urls. Of course certain characters aren't allowed to be entered but they do it anyway.So I want to set a rule on this.

Let's for simplicity just take spaces.

I select the column in question, go to Data>Data Validation, select Allow>Custom and as the formula enter SUM(IFCOUNT(" "))>0. This doesn't work, it generates an error on every value in that column, even if it doesn't have any spaces in it. I also, for spaces, used TRIM(), SUBSTITUTE(" ", "") but they all lead to the same result.

So, what would be the best way to prevent a user to enter or add a space, or any other illegal character, in a cell in a specific column in an Excel sheet?

Thanks in advance.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Willem
  • 81
  • 2
  • 9

1 Answers1

0

Here is one of the options you can implement (using VBA). Regarding restricting user Inputs I would personally use UserForms over direct input into the worksheets as in UserForms you have more control over what users can and what they cannot do.

Public bUpdate As Boolean
Sub Worksheet_Change(ByVal Target As Range)
    If bUpdate = False Then 'avoid infinite loop
        bUpdate = True 'flag start

        Dim sCharacters() As Variant
            sCharacters = Array(" ", ",", ".")'list of Invalid Characters
        Dim sItem As Variant

        For Each sItem In sCharacters
            Target.Value = Replace(Target.Value, sItem, vbNullString)
        Next sItem

        bUpdate = False 'flag process ended
    End If
End Sub
Robert J.
  • 2,631
  • 8
  • 32
  • 59