CODEWITHSUNDEEP
excelvba
diem_L
diem_L

Reputation: 399

Worksheet.CommandButton generates Method not found error

For the following code I get this error

Method or Data Member not found.

Private Sub Workbook_Open()
    Dim ws1 As Worksheet: Set ws1 = Worksheets("Sheet1")

    With ws1.CommandButton1   ' Error apears here
        'Set Top/Height/Width/Left
    End With
End Sub

The following code works:

Private Sub Workbook_Open()
    With Worksheets("Sheet1").CommandButton1
         'Set Top/Height/Width/Left
    End With
End Sub

I want to write a shorter macro and not always write Worksheets("...").CommandButton..

Upvotes: 1

Views: 782

Answers (1)

rohrl77
rohrl77

Reputation: 3337

You could use the following to do the same:

Private Sub Workbook_Open()
    Dim obj As Object
    Set obj = Sheet1.CommandButton1
    With obj   ' Error apears here
        'Set Top/Height/Width/Left
    End With

End Sub

What I'm doing in my example is using the the code name of the worksheet rather than the regular name.

I think the reason why the first version you posted doesn't work, is that CommandButton1 is not part of a worksheet's object model.

Upvotes: 2

Related Questions