CODEWITHSUNDEEP
vbaexcelruntime-error
cimbom
cimbom

Reputation: 259

VBA Application.Caller runtime error

I have the following code in VBA:

Sub Kontrollkästchen_KlickenSieAuf()
With ThisWorkbook.Sheets("Hinterlegungsmatrix Auswahl")
Dim i, j, rowx, columnx As Integer
rowx = Application.Caller.row 'I got here the run time error (object required)-->.row doesn't work
columnx = Application.Caller.column 'I got here the run time error (object required)-->.column doesn't work
        If Worksheets("Hinterlegungsmatrix Auswahl").Cells(rowx, columnx).Value = True Then
              For i = 6 To 22
                For j = 3 To 22
                    If (Worksheets("Hinterlegungsmatrix Auswahl").Cells(i, j).Interior.Color = RGB(250, 192, 144)) Or (Worksheets("Hinterlegungsmatrix Auswahl").Cells(i, j).Interior.Color = RGB(83, 142, 213)) Or (Worksheets("Hinterlegungsmatrix Auswahl").Cells(i, j).Interior.Color = RGB(242, 221, 220)) Then
                      Worksheets("Hinterlegungsmatrix Auswahl").Cells(i, j).Value = True
                    End If
                Next j
            Next i
        End If
End With
End Sub

I would like to get the Cell where the Checkbox is activated, but it throws a run time error, wenn I use Application.Caller.Address or .Row or .Column. I assigned the checkbox the sub Kontrollkästchen_KlickenSieAuf() I would be very happy, if someone can help me.

Greets

Upvotes: 2

Views: 2688

Answers (1)

Rory
Rory

Reputation: 34045

Application.Caller is a String (the name of the control) not an Object, and the checkbox doesn't have a row property. You need to use:

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

for example.

Upvotes: 4

Related Questions