Worksheet change VBA

Question

I've been working Worksheet_Change VBA code in Excel as shown below.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then

Call writetag

End If

End Sub

Sub writetag()

    ActiveCell.Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Formula = "1st Request"

End Sub

The writetag VBA code alone does its job just fine wherein it would move 1 cell to the right of the active cell and write "1st Request". Then I have the first VBA code - Worksheet_Change - that will trigger the writetag code whenever there are changes made on range A2:A2000. But it is at this part that the writetag code does not work perfectly. Instead of moving 1 cell to the right, it would move 1 cell to the right and another 1 cell below. So I need to adjust ActiveCell.Offset(0, 1).Select to ActiveCell.Offset(-1, 1).Select just so that it would move to the right cell. Then after that, I would like to make 3 conditions or Ifs, wherein when I put 1 anywhere on the A2:A2000 range, it will put "1st Request" to its right. If I put 2 anywhere on the range, it will put "2nd Request" to its right, "3rd Request" if I put 3.

Thank you so much for the help.

Also worth a [READ](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) since you are working with `Worksheet_Change` — Siddharth Rout, Jan 13 '20 at 10:21

score 0 · Answer 1 · answered Jan 13 '20 at 05:29

0

Use following sub when you enter value and press ENTER from keyboard.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then
        Call writetag
    End If
End Sub

Sub writetag()
Dim curr As Variant
    curr = ActiveCell.Offset(-1, 0)
    ActiveCell.Offset(-1, 1) = curr & "st Request"

End Sub

answered Jan 13 '20 at 05:29

Harun24hr

30,391
4
21
36

Thank you. This worked well, except for the `"st Request"` part since it would not fit when I need "2nd Request", "3rd Request", etc to fill. But this idea definitely will be useful to some of my projects. – Rich Padama Jan 13 '20 at 06:15

score 0 · Accepted Answer · answered Jan 13 '20 at 05:52

Remember target is the address of the called cell, in your case suppose you entered in cell A1:

target = [A1] but the problem is that this event fires after the value is changed so after press enter ActiveCell = [A2], then the event is called and the result is

ActiveCell.offset (0,1) = [A2] .offset (0,1) = [B2]

so your code is not working, let's try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then
        Call writetag(Target)
    End If
End Sub
Sub writetag(rng As Range)
    With rng.Offset(0, 1)
        Select Case rng.Value2
            Case 1
                .Formula = "1st Request"
            Case 2
                .Formula = "2nd Request"
            Case 3
                .Formula = "3rd Request"
        End Select
    End With
End Sub

Thank you. This worked perfectly well! I'm so grateful for the quick and elegant solution. — Rich Padama, Jan 13 '20 at 06:13

Worksheet change VBA

2 Answers2