mercredi 29 juillet 2015

Excel Application.Caller Error 2023

I created a long column of check boxes in Column M, and I would like to have a macro that displays the username in Column N and the date in Column O. Here's what I have thus far:

Sub CheckBox_Click()

    Dim cb As CheckBox
    Set cb = ActiveSheet.CheckBoxes(Application.Caller)

    Set NLoc = cb.TopLeftCell.Offset(0, 1)
    Set OLoc = cb.TopLeftCell.Offset(0, 2)

    If cb.Value = xlOn Then
        NLoc.Value = Application.UserName
        OLoc.Value = Now()
    Else
        NLoc.Value = Null
        OLoc.Value = Null
    End If
End Sub

This works fine if I replace Application.Caller with the name of each check box, but, as I have many check boxes, I would like to be able to do this generically. However, when I try to compile, I get:

Run-time error '91':
Object variable or With block variable not set

When I hover over Application.Caller, it says

Application.Caller = Error 2023

How do I fix this?




Aucun commentaire:

Enregistrer un commentaire