[RESOLVED] Excel VBA - Worksheet_Change Function

Hi,

I am setting up a worksheet that contains some vba functions and among them, I wrote a code with Worksheet_change in order to call up a userform if the cell value in the target changes to 'sent'. Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D5:D31")) Is Nothing Then

Select Case Target.Cells.Value
Case "Sent"
OrderSentTime_userform.Show
End Select

Else
Unload OrderSentTime_userform
Exit Sub

End If

End Sub

The code works perfectly on its own. However, it doesn't when i fit it into the whole worksheet code, especially when i run another code that trying to clear the content of selected rows. And the error has been referred to the (case "Sent") as above. I couldn't figure out the reason for that. Could you please advice what's wrong to the code?

Apologies if my explanation isn't clear enough, but i hope my sample worksheet attached help better.

Thank you.

xwxc

I would start with formatting the code so its easier to read.

Code:

---

    If Not Intersect(Target, Range("C5:C31")) Is Nothing Then
        Select Case Target.Cells.Value
            Case "Sent"
                MsgBox ("Input Time")
        End Select
    Else
        Exit Sub
    End If

---

But when you look at it you can see that a select case is not needed since there is only one case statement.

Code:

---

    If Not Intersect(Target, Range("C5:C31")) Is Nothing Then
        If Target.Cells.Value = "Sent" Then
            MsgBox ("Input Time")
        End If
    Else
        Exit Sub
    End If

---

What do you mean "whole worksheet code"? Do you mean if you place it in the "ThisWorkbook" class?

Hi,

thanks for the amended code.

I meant to put the worksheet_change code into the active worksheet, not the 'this workbook'.

When i change the cell value in C5:C31 to 'sent', the code works perfectly, and that's what i wanted.
But when i check the box to clear the content of row(s), with the 'clear' command button, a debug window pops out saying [run-time error'13': type mismatch], and then highlight the code [If Target.Cells.Value = "Sent" Then].

I am not sure if the command button code contains anything that crash with the 'worksheet change code'?

thx

xwxc

problem arises when target is multiple cells, limit code to only work if target is single cell

Code:

---

    If Not Intersect(Target(1), Range("C5:C31")) Is Nothing Then
        If Target(1).Value = "Sent" Then
            MsgBox ("Input Time")
        End If
    End If

---

Hi, many thanks, the code works perfectly.

Do you mind to explain to me why the additional of #1 next to "target" makes such a different? I tried to do a search but couldn't be satisfied.

Appreciate your assistance

Thx,

xwxc

Target is a range, it may have only one cell (such as when user change a cell in column C) or a range of cells (such as when the code clear a row).

When Target is a single cell, Target.Value returns a single value that can be used to compare with string value "Sent" without problem.
When Target has two or more cells, Target.Value returns an array that CANNOT be used to compare with string value "Sent". This is why you hit the error on clearing.

The use of Target(1) to make sure that you test only a single cell, that is the first cell in the Target range.

Target(1) is the shortcut of Target.Cells(1,1).

In more general cases, such as when user copy and paste to multiple cells at the same time then we need to test every cell in the Target range (but need to prompt only one MsgBox):

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CheckRange As Range
    Dim aCell As Range
   
    Set CheckRange = Intersect(Target, Range("C5:C31"))
    If Not CheckRange Is Nothing Then
        For Each aCell In CheckRange
            If aCell.Value = "Sent" Then
                MsgBox ("Input Time")
                Exit For
            End If
        Next
    End If
End Sub

---

Many thanks

xwxc