VBA: Declare a cell range as a value for a Constant

[KP_SoCal]

Instead of using a Dim statement in the code listed below, I would like to make this into a Constant and set the value to a specific range. So instead of a the current Dim statement being Dim ocell in Target.Cells, I would have a statement something like Const ocell As String = Range("F2:G16,K2:L16"). Unfortunately I have not been able to get this to work as a Constant. Any ideas what I'm doing wrong? Thanks!


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]

wigi, thanks so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I was toying with this for hours trying to get it to work and the code you provided brings it together beautifully! Also, you bring up a good point. I should be using code tags. I will do this going forward. Thanks again!