Macro code works but same code in ControlButton will not.

[ColinCoady]

If I run this code as a macro it works fine but put same code in for a command button and I get a Run-time error '1004': Select method of range class failed.
Anyone help with a fix?

This is where the debug stopped in the code:

Private Sub CommandButton3_Click()
'
' DeleteInputData Macro
' Deletes input data cells on sheets and objects
'


'
    Dim sh As Shape
    
    Range("B3:B11,B13:E13,B14:F14,B15").Select
    Range("B15").Activate
    Selection.ClearContents
    Range("B3").Select
    Sheets("Costing").Select
    Range("A9:C17,D9,D12,D15,D18,B20,B22,B24").Select
    Range("B24").Activate
    Selection.ClearContents
    Range("D9").Select
    
    Sheets("Docs").Select
    For Each sh In ActiveSheet.Shapes
    sh.Delete
    Next sh
    
    Sheets("InfoEntry").Select
    Range("B3").Select
    
End Sub

 

[MB110]

Create a new macro for your button and call this function.

Sub NewCommandButtonName_Click()
Call CommandButton3_Click
End Sub

I think this is a Private Sub vs Sub thing. You can call a private sub from elsewhere though.

 

[MB110]

Sorry, I misunderstood your issue. Running it from ActiveX control does give the runtime error.

 

[ColinCoady]

No didn't work - same thing happened
Think of anything else?

-Colin

 

[MB110]

Check this tread. Maybe it could help.

https://www.mrexcel.com/forum/excel-questions/19962-method-range-class-failed-solved.html

 

[MB110]

I believe your answer is here.

https://stackoverflow.com/questions/3902818/select-method-of-range-class-failed-via-vba

 

[MARK858]

@ColinCoady, what is the name of the sheet when the code begins?

 

[ColinCoady]

sheet name is "InfoEntry"

-Colin

 

[MARK858]

What happens with the code below?

Private Sub CommandButton3_Click()
    '
    ' DeleteInputData Macro
    ' Deletes input data cells on sheets and objects
    '


    '
    Dim sh As Shape

    Sheets("InfoEntry").Range("B3:B11,B13:E13,B14:F14,B15").ClearContents
    Sheets("Costing").Range("A9:C17,D9,D12,D15,D18,B20,B22,B24").ClearContents

    For Each sh In Sheets("Docs").Shapes
        sh.Delete
    Next sh

    Sheets("InfoEntry").Select
    Range("B3").Select

End Sub

 

[ColinCoady]

It deletes all the pictures, objects, shapes etc on the "Docs" sheet - one at a time until the are all deleted.