Worksheet_Change Problem

[Philip West]

Hello Everyone,
I'm having a problem with some code that i'm trying to make work. I'll start by saying that this code is the product of a mornings frantic Googleing and I've never written anything in VBA before.

In my spreadsheet, each row in column W has a validation list, with 3 options, one of which is 'Review'. In Column T i have an ID that should be unique per row, but only generated when Review is selected from the list in W. What i thought i would be able to do is create a little bit of code that activates when one of the lists is changed, then checks if that rows ID is already there (so as not to overwrite anything), and if not, creates a new ID (max()+1).

Now, the code I've posted here works just fine if I write Review into a cell that's part of the W8:W500 range. I type review, a msg box pops up and then a new number is written on the same row, but in column T. The problem I am having is that when I select Review from the validation list I get the msg box popup with the correct information but then it doesn't add the number to column T.

I have played about with this most of today and i'm really struggling to work out what I've done wrong. So i'm hoping that someone can see something obvious that I've done wrong.

Thanks in advance.
Philip.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
       
    Dim KeyCells As Range
    Dim NextID As Integer
   
    NextID = Application.WorksheetFunction.Max(Range("T8:W500")) + 1
   
    Set KeyCells = Range("W8:W500")
   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
          Is Nothing Then
       
        If Target.Value = "Review" Then
            If IsEmpty(Range("T" & Target.Row)) Then
                MsgBox "T" & Target.Row & " " & NextID
                Range("T" & Target.Row).Value = NextID
            End If
        End If
    End If
   
End Sub

 

[NARAYANK991]

Hi Philip ,

On the face of it , there is nothing wrong with your code ; however , please remember that a Worksheet_Change event is triggered when ever a worksheet cell is changed , either by a user , or by code itself.

Every Worksheet_Change event procedure should have the following statement :

Application.EnableEvents = False

prior to a section that will change a worksheet cell / range , and follow it later with :

Application.EnableEvents = True

Can you change your code to incorporate these two lines and see whether it changes anything ?

If , as you say , the message box is being displayed , there is no reason why the value should not be entered into the cells in column T.

Private Sub Worksheet_Change(ByVal Target As Range)
            If Target.Cells.Count > 1 Then Exit Sub
     
            Dim KeyCells As Range
            Dim NextID As Integer
 
            NextID = Application.WorksheetFunction.Max(Range("T8:W500")) + 1
 
            Set KeyCells = Range("W8:W500")
 
            If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
              If Target.Value = "Review" Then
                  If IsEmpty(Range("T" & Target.Row)) Then
                    MsgBox "T" & Target.Row & " " & NextID
                    Application.EnableEvents = False
                    Range("T" & Target.Row).Value = NextID
                    Application.EnableEvents = True
                  End If
              End If
            End If
End Sub

Narayan

 

[Philip West]

Hi Narayan, Thanks for the reply. I added those lines and it made no difference. However when trying the same spreadsheet on other computers everything works as intended so I'm not sure what that's about but i'm going to put it down to my computer being silly for now.

 

[SirJB7]

Hi, Philip West!
All the computers are running under the same OS (Windows or iOS) and same Excel versions?
Any chance of uploading a sample file?
Regards!