Trying to update one or group of cells on change with VBA

[pb0759]

Hello,
I am trying to update when users change one or group of cells within a range when there is a space to replace with an underscore. It is working, however with a group a cells, perhaps like copy or control enter, the values only update after you click on each cell. Works okay when one cell is clicked.
Example if select at range of Cells an input "Location 1" then expected result would be Location_1.



Private Sub Worksheet_SelectionChange(ByVal Target as Range)
Dim loc as Range, res as string
Set loc = Range("A1:A20")

If Not Application.Intersect(loc, Range(Target.Address)) _
Is Nothing Then

Application.EnableEvents = False

For Each loc in Target
res = (Replace(loc.Value, Space(1), "_")
loc = res
Next loc

Application.EnableEvents - True

End If

End Sub

 

[DanteAmor]

You must use the change event.


Try this, I added a pack of validations.

Private Sub[COLOR=#0000ff] Worksheet_Change[/COLOR](ByVal Target As Range)
    Dim c As Range, loc As Range, n As Long
    Set loc = Range("A1:A20")
    If Not Intersect(Target, loc) Is Nothing Then
        Application.EnableEvents = False
        For Each c In Target
            If Not Intersect(c, loc) Is Nothing Then
                c.Value = Replace(c.Value, " ", "_")
            End If
            n = n + 1
            If n > loc.Count Then Exit For
        Next
        Application.EnableEvents = True
    End If
End Sub

 

[pb0759]

Thank you, however its the same as the one I posted. The cells update, but only when you click on each one after.

 

[pb0759]

I have changed to For Each c in loc (vs Target) and the cells update once I click outside of the range. Thanks for your help on this matter.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[pb0759]

Again thanks for all of your quick help.
I have another question to add to this code..
If I have several ranges in different columns how can I make this work for different rules?
Perhaps a case statement somehow?

Kindest regards,

 

[DanteAmor]

Could be

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, loc As Range, n As Long
    Set loc = Range("A1:A20")
    Set [COLOR=#0000ff]loc2 [/COLOR]= Range("C1:C20")


    If Not Intersect(Target, loc) Is Nothing Then
        Application.EnableEvents = False
        For Each c In Target
            If Not Intersect(c, loc) Is Nothing Then
                c.Value = Replace(c.Value, " ", "_")
            End If
            n = n + 1
            If n > loc.Count Then Exit For
        Next
        Application.EnableEvents = True
    End If

    If Not Intersect(Target, [COLOR=#0000ff]loc2[/COLOR]) Is Nothing Then
        Application.EnableEvents = False
        For Each c In Target
            If Not Intersect(c, loc) Is Nothing Then
                c.Value = Replace(c.Value, " ", "_")
            End If
            n = n + 1
            If n > loc.Count Then Exit For
        Next
        Application.EnableEvents = True
    End If


End Sub

 

[pb0759]

Yes, Thank you very much.
This works, however I am not sure why even before the second loop added my excel frozen to open.

 

[DanteAmor]

Do you have other macros?

 

[pb0759]

Yes, I have other ones all thru out the workbook in modules.
I noticed that it has something to do with the test sheet I was working on. I removed it, then now it opens faster.

So weird.