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

 

[wigi]

Perhaps you meant:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const oRange As String = "F2:G16,K2:L16"

    Dim oCell As Range

    For Each oCell In Range(oRange)
        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

Wigi

 

[lenze]

How many cells are you changing at ONE time? Are you changing them manually??

lenze

 

[KP_SoCal]

wigi, thanks! Now it works.

lenze, basically it's one cell at a time. If an end user enters a value into a cell with a "space" in it, this code will essentially trim the spaces out of the entry. It gets the job done, however, the longer the cell range for my constant, the longer the processing time. If there's a better way to go about accomplishing this, I'm totally open. Thanks!

 

[wigi]

This will be a lot better:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F2:G16,K2:L16")) Is Nothing Then
        Application.EnableEvents = False
        Target.Value = Replace(Target.Value, " ", "")
        Application.EnableEvents = True
        MsgBox "! Spaces are not allowed and have been trimmed"
    End If
End Sub

 

[lenze]

Just saw wigi got it for you, but this might work too!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Union(Range("F2:G16"),Range("K2:L16"))) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target = Trim(Target)
Application.EnableEvents = True
End Sub

Just saw wigi got it for you, but this might work too!

 

[KP_SoCal]

Wigi, this processes much much much faster! Thanks!!! However, the only downfall to this code is that adding or deleting a value of a cell will prompt me with the message box stating the spaces are not allowed. My original code would only prompt me when spaces were found in the cell.

Lenze, thanks for the reply. I did go ahead and apply Wigi's suggestion.

 

[lenze]

Do you really need the prompt?? It can be added

If Len(Target) <> Len(Trim(Target)) Then
            Target = Trim(Target)
            MsgBox "! Leading/trailing spaces are not allowed and have been trim
End If

lenze

 

[KP_SoCal]

Thanks guys for these suggestions. To make things simple, I decided to eliminate the need for the message prompt. So now the following code accomplishes what I need it to:

''////TRIMs values entered with SPACES
If Not Intersect(Target, Range("B6:J50000")) Is Nothing Then
Application.EnableEvents = False
Target.Value = Replace(Target.Value, " ", "")
Application.EnableEvents = True
'MsgBox "! Spaces are not allowed and have been trimmed"
End If

The worksheet change even is what fires it. However, I have one final dilemma. In addition to trimming spaces, I have another string of code that needs to fire on the worksheet change event as well. The code is written below. Lenze, you were actually the one that originally provided the code. . Anyhow, both of sets of codes work perfectly by themselves, however I need to bring them both together under of worksheet change event.

It tried combining them on my own, but it creates an error right around the lines of code that I have highlighted in red. If this code could be written using a similiar logic as the code highlighted in blue, I think it would work. Instead of using "Application.Undo", I could set the target to "". I'm just not sure how to go about it. You guys are the experts, thanks again for your help!


If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("I6:J50000")) Is Nothing Then Exit Sub
Select Case Target.Column
Case 9 'Cell Column Number (currently set to column I)
If Cells(Target.Row, "J") <> "" Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
ActiveCell.Offset(0, 1).Select
Call MsgActiveCell
Else: 'MsgBox "Allowed"
End If
Case 10 'Cell Column Number (currently set to column J)
If Cells(Target.Row, "I") <> "" Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
ActiveCell.Offset(0, -1).Select
Call MsgActiveCell
Else: 'MsgBox "Allowed"
End If
Case Else:
End Select

 

[wigi]

For instance:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Count = 1 Then
        If Not Intersect(Target, Range("B6:J50000")) Is Nothing Then
            Target.Value = Replace(Target.Value, " ", "")
            Select Case Target.Column
            Case 9, 10   'Cell Column Number (currently set to column I)
                If Cells(Target.Row, IIf(Target.Column = 9, 10, 9)) <> "" Then
                    Target.ClearContents
                    Cells(Target.Row, IIf(Target.Column = 9, 10, 9)).Select
                    Call MsgActiveCell
                End If
            End Select
        End If
    End If
    Application.EnableEvents = True
End Sub

Please use code tags when you post code on the forum - thanks.

Wigi