Worksheet_Change problem

[breynolds0431]

Hello:

Here is my issue... when data is entered into Range("PymtProviders"), I wanted a macro to convert the first two numbers to a number value in an adjacent column Range("PymtOut"). But for some reason, its not working on the Private Sub Worksheet_Change. It does, however, work as a Public Sub.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim SourceRange As Range, DestinationRange As Range, i As Integer
     
    Set SourceRange = Sheet3.Range("PymtProviders")

    Set DestinationRange = Sheet3.Range("PymtOut")

    For i = 1 To SourceRange.Count
    
    DestinationRange(i, 1).Value = Left(SourceRange(i, 1).Value, 2)

    Next i
    
End Sub

Does anybody see an issue with the Worksheet_Change? Thank you for any help in advance.

 

[Damon Ostrander]

Hi breynolds0431,

I think the problem most likely is that your routine is being executed recursively. This is because each time your loop modifies the value of DestinationRange, this triggers the Worksheet_Change event again. The easiest way to stop this is to turn events off until the routine completes, like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim SourceRange As Range, DestinationRange As Range, i As Integer
     
    Set SourceRange = Sheet3.Range("PymtProviders")

    Set DestinationRange = Sheet3.Range("PymtOut")

    Application.EnableEvents = False

    For i = 1 To SourceRange.Count
    
    DestinationRange(i, 1).Value = Left(SourceRange(i, 1).Value, 2)

    Next i

    Application.EnableEvents = True

    
End Sub

 

[breynolds0431]

Worked like a charm. Thanks very, very much.

 

[breynolds0431]

Hi Damon:

Do you think having a worksheet_change event like this would make the rest of the sheet sluggish? It seems like there is a prominent lag after each entry. Is there a way to trigger after a Range Change?

I also have a Worksheet_Calculate event. Is there a way to specify only to trigger if a calculation change occurs in a certain range?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("PymtProviders")) Is Nothing Then
    End If

'Looks at Provider ID Number and determines state of facility
 
    Dim SourceRange As Range, DestinationRange As Range, i As Integer
     
    Set SourceRange = Sheet3.Range("PymtProviders")

    Set DestinationRange = Sheet3.Range("PymtOut")

    Application.EnableEvents = False

    For i = 1 To SourceRange.Count
    
'Changes SourceRange to a value for purposes of VLookup function
    
    DestinationRange(i, 1).Value = Left(SourceRange(i, 1).Value, 2)

    Next i
    
    Application.EnableEvents = True
    
End Sub

Private Sub Worksheet_Calculate()

'Determines State/County for Urban/Rural Designation

    Dim SourceRange As Range, DestinationRange As Range, i As Integer
     
    Set SourceRange = Sheet2.Range("BLENDEDIN")

    Set DestinationRange = Sheet2.Range("BLENDEDOUT")

    Application.EnableEvents = False

    For i = 1 To SourceRange.Count
    
'Changes SourceRange to a value for purposes of VLookup function
    
    DestinationRange(i, 1).Value = SourceRange(i, 1).Value

    Next i

    Application.EnableEvents = True

End Sub

Thanks again.