VBA to Create Message Box when Space Bar is Pressed

[KP_SoCal]

For colums A,B,C,G,H on my worksheet, I would like to create an error message when users press the [SPACE BAR] when inside any one of the cells for these columns. The message would prompt them that entries using the [SPACE BAR] are not allowed.

I took a stab at this by writing the following code below, but it did not work for me. Any ideas on how to accomplish this? Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If KeyAscii = 32 Then
MsgBox "Space is not allowed!", vbOKOnly
Application.EnableEvents = Flase
Application.Undo
Application.EnableEvents = True
Else
End If
End Sub

 

[mvptomlinson]

You can not trigger an event while in cell edit mode, only after the cell has changed (or a new cell selected). You would need to use a textbox for that type of functionality.

In the Change event, just check to see if what the user entered has a space, and remove the space or clear the entire cell and provide a message box to the user to re-enter.

 

[KP_SoCal]

Ok, that makes sense then. Perhaps there is some VBA code that could be triggered after the value has been entered or a when a new cell is selected? The event would essentially search the cell for any spaces and fire a message box if any were found. I’m guessing that a search function would somehow have to be incorporated within the code??? Any other ideas???

 

[JmME102]

Try this to see if it does the trick:
Put this in the worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ocell As Range
For Each ocell In Target.Cells
If Len(ocell.Value) <> Len(Trim(ocell.Value)) Then
ocell.Value = Trim(ocell)
MsgBox "! Leading/trailing spaces are not allowed and have been trimmed from : " & ocell.Address
Else
If Len(Replace(ocell.Value, " ", "")) <> Len(ocell.Value) Then
ocell.Value = Replace(ocell.Value, " ", "")
MsgBox "! Embedded spaces are not allowed and have been trimmed from : " & ocell.Address
End If
End If
Next
End Sub

 

[KP_SoCal]

JmME102, this works excellent for the entire sheet. However, is there a way to modify this code to only apply to cell within the following range: F2:G16,K2:L16.

Again, this code is really slick, thanks! If you can get it to apply to a specific range it will go from "slick" to "perfect"!

 

[KP_SoCal]

Sorry, I wanted to include this in my response. Instead of using the Dim statement, I attempted to write a Conts statement specifying the range I need, but unfortunately it didn't work. Maybe there is a better way?

'Dim ocell As Range
Const ocell As String = Range("F2:G16,K2:L16") <--Didn't work

 

[mvptomlinson]

This should work

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("[COLOR=black]F2:G16,K2:L16")) Is Nothing Then[/COLOR]
    Target.Value = Trim(Replace(Target.Value, " ", ""))
    MsgBox "Spaces are not allowed and have been removed from the previous entry in " & Target.Address
End If
End Sub

<!-- / message -->

 

[mvptomlinson]

Add two lines to the code so Excel doesn't crash on you..(code in red)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("[COLOR=black]F2:G16,K2:L16")) Is Nothing Then[/COLOR]
[B][COLOR=red]Application.EnableEvents = False[/COLOR][/B]
    Target.Value = Trim(Replace(Target.Value, " ", ""))
    MsgBox "Spaces are not allowed and have been removed from the previous entry in " & Target.Address
[COLOR=red][B]Application.EnableEvents = True[/B][/COLOR]
End If
End Sub

<!-- / message -->

 

[KP_SoCal]

mvptomlinson, thanks for the fix! I'm in business now!!!