VBA data validation

[Russk68]

Hi All
I am using data validation for a list in column C. I also need to prevent a single space from being entered in column C, so I would like to use code in column C3:C5000 with a message "No space allowed"

Thank you in advance!

 

[tyija1995]

Hey,

Highlight cells C3:C5000 and try a Custom data validation rule as such:

=COUNTIF($C$3:$C$5000,"* *")=0

Then you can write an error alert that says "No space allowed"

 

[tyija1995]

Using Record Macro it comes out as this in VBA:

Sub ADv()
'
' ADv Macro
'


'
    Range("C3:C5000").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=COUNTIF($C$3:$C$5000,""* *"")=0"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "No Space Allowed"
        .InputMessage = ""
        .ErrorMessage = "Please do not enter spaces in these cells."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[Russk68]

Hi Tyija
I pasted your code in the sheet where I want this applied. I can still type in a space or a word with a space. Any idea what I could be doing wrong?

Thank you for your help!

 

[tyija1995]

Hi Tyija
I pasted your code in the sheet where I want this applied. I can still type in a space or a word with a space. Any idea what I could be doing wrong?

Thank you for your help!

Hi Russk, put the code in to a module rather than the sheet - then run the code and it should apply to your activesheet.

You can edit the code to target a specific sheet by adding the following code:

Sheets("Your sheet name goes here").Activate

Place that BEFORE the Range("C3:C5000").Select

EDIT: This should work better than previous code:

Sub ADv()
'
' ADv Macro
'




'
    Sheets("Your sheet name goes here").Activate
    Range("C3:C5000").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=COUNTIF(C3,""* *"")=0"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "No Space Allowed"
        .InputMessage = ""
        .ErrorMessage = "Please do not enter spaces in these cells."
        .ShowInput = True
        .ShowError = True
    End With
End Sub

 

[Russk68]

Thank Tyija
That works but...
The validation list that I was using in that column is gone.
I always wondered if it is possible to have more than one validation rule. Is vba the only way to do it?
Would I need to add code to get the list working again?

 

[tyija1995]

You can combine rules to a certain extent, depending on the criteria, but technically you can only have 1 data validation rule active for a certain cell.

In this case the macro will make your desired tab in the range C3:C5000 - not allow any spaces. If you had a previous rule before in the same range, the code does overwrite it - what exactly was the previous rule?

 

[Akuini]

Try this:
Put the code in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C3:C5000")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            If InStr(Target.Value, " ") Then
            Target.Activate
            MsgBox "No space allowed"
            End If
        End If
    End If
End Sub

 

[Russk68]

It was just a list.

 

[Russk68]

That helps alot! I'm still allowed to type a space but I added a couple of lines of code to replace the formula which I was trying to protect.

Thank you!