Worksheet_Change problem

breynolds0431

Active Member
Joined
Feb 15, 2013
Messages
303
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.


Code:
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:

Rich (BB code):
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
 
Last edited:
Upvote 0
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?

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top