Clear Button error

  • Hi please can someone help? I'm using Excel to write a simple factory picking list. I want to be able to clear the contents on Sheet- 'Pick Lists' row 7 to 41 by clicking a command button on Sheet 'Master 800'. I recorded a macro for this purpose and it works perfectly, however, when I paste the macro into a command button I get an error message stating: "runtime error 1004, select method of range class failed". I don't understand why it's falling down when it works ok as a stand alone macro?. Anyway, anybody's help would be much appreciated.


    Cheers


    Joecod

  • Re: Clear Button error


    Joecod,


    Why doesn't your macro work when assigned to the command button but works when ran as a stand alone macro.


    You need to learn that if/when you don't specify an object that excel will use the default object. The next thing that you need to learn is what is the current default object at a particular time.


    Ok, the difficulty here is in my not being able to explain myself. But I'll try. In your code you essentially are only concerned with ranges. "D10", "7:41", and "B5:B6".


    Now VBA ( the language of macros ) is an Object Oriented language. Which means that it uses objects. To see a list of the objects in Excel VBA press F1 when in the Visual Basic editor and search on "Microsoft Excel Objects" there you will get a diagram of the objects, along with so much more.


    Now the sweet thing about this is that if you don't specify a parent object that it has the particular default object ready to use ( this is nice as it cuts down on how much you have to type )


    Now seeing how you used ranges as your lowest object I'll just work with that.


    So know your code was on the MODULE PAGE for the worksheet "Master 800", I've numbered your lines of code to help explain.


    Now because you are on a module page for a worksheet the default worksheet object is


    SHEETS("Module 800") ** or ** WORKSHEETS("Module 800")


    *** Remember you can't SELECT a range unless you are on that page ( no exceptions )


    Line 1:
    You didn't specify a sheet so the default is used. No issue because the activesheet is the same as the default parent for that command


    Line 2:
    Now you activate a different sheet. But this code is still running with the assumption of what the default worksheet object is BECAUSE of what type of module sheet it's on.


    Line 3:
    Here is where you're problem lies. You want to select rows 7 to 41. But you don't specify a parent ( worksheet object ) so the default is used ( WHAT IS THE CURRENT DEFAULT OBJECT ). The default is Sheets("Module 800") and can you select a range on a page that you aren't on? NO!
    Hence Norie's suggestion to use

    Code
    Sheets("Pick Lists").Rows("7:41").ClearContents


    You also could have used the following

    Code
    ActiveSheet.Rows("7:41").ClearContents
    Worksheets("Pick Lists").Rows("7:41").ClearContents


    Code
    Private Sub CommandButton1_Click()
    1    Range("D10").Select
    2    Sheets("Pick Lists").Select
    3    Rows("7:41").Select
    4    Selection.ClearContents
    5    Range("B5:B6").Select
    6    Sheets("Master 800").Select
    7    Range("D10").Select
    End Sub



    Hope it helps.

  • Re: Clear Button error


    I dont know if that helped out Joe but I appreciate you taking the time to write that out. It helped me understand some much needed information. Thanks iwrk4dedpr :rock:

    [SIZE=2]I should change my name to STUMBED![/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!