CODEWITHSUNDEEP
excelvbarepeat
Paul Deley
Paul Deley

Reputation: 13

How to repeat this function in VBA?

I have this function now in Excel VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count <> 1 Then Exit Sub

    If Target.Address = Cells(1, 5).Address Then
        Cells(1, 6) = Application.UserName
        Cells(1, 7) = Now
    Else
        Debug.Print "This was not B1"
    End If

End Sub

this works perfect for one cell on one line. Now I need to have it for multiple lines on this sheet. How do I do that? When just copying and updating the parameters in the Cells lines I get the error message that the eventname cannot be used twice.

Final solution for me would be that for all 15 lines on in this sheet I have this function.

Upvotes: 1

Views: 171

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can only have one SelectionChange event per worksheet, so you need to handle everything in that event.

If you want to do different things use:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.CountLarge <> 1 Then Exit Sub

    If Target.Address = Cells(1, 5).Address Then
        Cells(1, 6) = Application.UserName
        Cells(1, 7) = Now
    ElseIf Target.Address = … your other cell address … Then
        'do something else
    Else
        Debug.Print "This was not B1"
    End If

End Sub

If you want to do the same thing for multiple lines the do the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.CountLarge <> 1 Then Exit Sub

    If Not Intersect(Target, Me.Range(Me.Cells(1, 5), Me.Cells(10, 5))) Is Nothing Then
        Target.Offset(ColumnOffset:=1) = Application.UserName
        Target.Offset(ColumnOffset:=2) = Now
    Else
        Debug.Print "This was not B1"
    End If

End Sub

This will work from row 1 Me.Cells(1, 5) to row 10 Me.Cells(10, 5) and will write the username to column 6 and time to column 7 of the selected row of column 5 in that range.

Upvotes: 1

Related Questions