Print Button In Excel

  dogbreath1 17:35 28 Aug 10
Locked

Please have in mind that I am an Excel rookie!

I want to place PRINT buttons on an Excel 97 spreadsheet and I believe that this can be done using a macro. I need to be able to print the whole worksheet with one button and a predefined range with a second button.

Coherent step by step guide, anyone!!?? ;)

Also, I want an aesthetically pleasing method of flagging up a warning when a cell returns data beyond an acceptable range.

TIA

db.

  VoG II 17:47 28 Aug 10

View > Toolbars > Controls, click on the button icon and draw the button on the sheet (just like drawing a rectangle). Right click the button, select View Code and paste in


Private Sub CommandButton1_Click()
'Print used range
With Me
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
End With
End Sub



Add another button and add the code


Private Sub CommandButton2_Click()
'Print defined range
With Me
.PageSetup.PrintArea = "A1:E25"
.PrintOut
End With
End Sub


(change A1:E25 to suit)

On the sheet on the Controls toolbar click Exit design mode (top left icon). Try clicking the buttons!

To flag up data try Conditional Formatting click here

  dogbreath1 19:21 28 Aug 10

Thanks, VoG™.

Very clear.

Works great!

  dogbreath1 10:32 29 Aug 10

Is it possible to substitute an imported image for a drawn button in Excel 97 whilst maintaining the original button's functionality?

  VoG II 11:00 29 Aug 10

Then you need a different approach. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert button and paste in




Sub PrintAll()
'Print used range
With ActiveSheet
.PageSetup.PrintArea = .UsedRange.Address
.PrintOut
End With
End Sub

Sub PrintSome()
'Print defined range
With ActiveSheet
.PageSetup.PrintArea = "A1:E25"
.PrintOut
End With
End Sub



then press ALT + Q to close the code window. Right click an image, select Assign macro and assign PrintAll or PrintSome. Repeat for the other image.

  dogbreath1 14:59 29 Aug 10

Getting Compile error: Invalid outside procedure when clicking either new image??

  dogbreath1 15:12 29 Aug 10

Also looking how to insert a button which can toggle protection to the range L7:L14, to enable users to edit default settings.

The whole sheet is protected by at the moment.

  VoG II 16:12 29 Aug 10

Which line of code is highlighted when you get the compile error?

  dogbreath1 16:35 29 Aug 10

Strange thing is....none. I can't even delete macros on the sheet as the modules are not showing up in the VB editor.

  dogbreath1 16:36 29 Aug 10

Can't delete images in the second version of the sheet or the drawn buttons in the first version.

  VoG II 16:39 29 Aug 10

First you need to unprotect the sheet. Then you should be able to delete the images. To delete the buttons click the Design Mode icon on the Control toolbar then select the button and press the Delete key.

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

20 groundbreaking 3D animation techniques

How to mine Bitcoin on Mac