VB Code Issue

[SV18]

Hi all,

I have the following code which isn't exactly how I want it to. The problem I have is the code is set on the change property of the worksheet, so when lines of the code make a change to a cell the macro tries to run itself again. It's really strange as I've not witnessed anything like this before, can anyone suggest anything?

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("B7:B21")) Is Nothing Then

If Left(Target.Value, 4) = "Tier" Then
ret = InputBox("Enter Investment Name", Left(Target.Value, 6) & " Investment Name")
Else
ret = Target.Value
End If
Target.Offset(0, 2).Value = ret

'Calculate the first Tier 1 fund (needed for the illustration.

Count = 1
Do While Count < 14 'Cycle through the rows and establish which is the first "Tier 1" fund - This will be used as an example on the illustration.
If Cells(Count + 6, 3) = 1 Then
Range("D23").Value = Cells(Count + 6, 4).Value
Range("M23").Value = Cells(Count + 6, 13).Value
Exit Do
End If
Count = Count + 1
Loop

End If
End Sub

 

[VoG]

Try

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("B7:B21")) Is Nothing Then
    Application.EnableEvents = False
    If Left(Target.Value, 4) = "Tier" Then
        ret = InputBox("Enter Investment Name", Left(Target.Value, 6) & " Investment Name")
    Else
        ret = Target.Value
    End If
    Target.Offset(0, 2).Value = ret
    
    'Calculate the first Tier 1 fund (needed for the illustration.
    
    Count = 1
    Do While Count < 14 'Cycle through the rows and establish which is the first "Tier 1" fund - This will be used as an example on the illustration.
        If Cells(Count + 6, 3) = 1 Then
            Range("D23").Value = Cells(Count + 6, 4).Value
            Range("M23").Value = Cells(Count + 6, 13).Value
            Exit Do
        End If
        Count = Count + 1
    Loop
    Application.EnableEvents = True
End If
End Sub

 

[SV18]

Excellent...thanks for that VoG, so simple yet so effective!