XL3: Can't Hide Button Objects Only

• Microsoft Excel for Windows, versions 3.0

SUMMARY

In Microsoft Excel 3.0, you may want to hide button objects when you print a document. For example, you may want embedded charts, rectangles, circles, lines, and text boxes to appear in the printout, but you do not want buttons to be printed. The macro in the "More Information" section of this article allows you to hide buttons, but leaves the other objects available for printing.

NOTE: In later versions of Microsoft Excel, you do not need to use a macro to hide button objects because this functionality is built in (buttons are hidden by default). If you want to print button objects, select the button and choose Object from the Format menu, select the Properties tab, and select the Print Object check box.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements for a particular purpose.

Sample Macro

   A1:     =ERROR(FALSE)
   A2:     =ACTIVATE("sheet1.xls")
   A3:     =SET.NAME("counter",1)
   A4:     =SELECT.SPECIAL(13)
   A5:     =RIGHT(SELECTION(),2)
   A6:     =FORMULA(A5,B16)
   A7:     =SET.NAME("objects",SELECTION())
   A8:     =WHILE(counter<=B16)
   A9:     =IF(ISERROR(SELECT(counter)),GOTO(A10),
            IF(GET.OBJECT(1)=7,HIDE.OBJECT()))
   A10:    counter=counter+1
   A11:    =NEXT()
   A12:    =PRINT?()
   A13:    =HIDE.OBJECT(objects,FALSE)
   A14:    =RETURN()

Explanation of Macro

A1: If the macro encounters an error, it will continue running.

A2: Activates the worksheet containing the buttons to be hidden, where

    "sheet1.xls" is the name of the spreadsheet.

A4: Selects all objects.

A5: Retrieves the right most number (which is the largest possible

    number of objects on a worksheet).

A7: Assigns to the name "objects" a list of all objects on the worksheet

    that will be used later to unhide the buttons after printing.

A9: If the current object number returns an error (meaning it doesn't

    exist), then the macro will continue incrementing "counter". If it
    doesn't return an error, the macro will test to see if it is a
    button. If this evaluates to true, the button will be hidden.

A11: Returns to beginning of While() loop.

A12: This will access the File Print dialog box, allowing you to make

     choices for printing the document. After choosing OK, the worksheet
     will print with no buttons.

     worksheet.

REFERENCES

"Function Reference," version 3.0