XL3: Can't Hide Button Objects Only

Last reviewed: April 28, 1995
Article ID: Q74470
The information in this article applies to:
  • 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.

A3: Sets "counter" to equal 1.

A4: Selects all objects.

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

    number of objects on a worksheet).

A6: Places the number retrieved in A5 to a blank cell in the macro sheet.

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.

A8: Runs the loop until it has tested all objects.

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.

A10: Incrementing "counter" by 1.

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.

A13: Uses the defined name of "objects" to restore the objects onto the
     worksheet.

A14: End the macro.

REFERENCES

"Function Reference," version 3.0


KBCategory: kbusage
KBSubcategory:

Additional reference words: 3.0 3.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 28, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.