macro runs fine in vb editor, but not on worksheet

[Corman]

Hello.

I have a macro, ran from a control button on the worksheet, that unprotects the worksheet, then resizes an object located on a chart, prints a range, then re-protects the worksheet.

When I press the button for the macro, only the printing is done (it does not resize the object). But, when I run the code line by line in the VB editor, it runs properly and resizes the object!

This is the code I'm using:

-----------
Sub PrintReport()

' Unprotect Worksheet
ActiveCell.Activate
Application.ScreenUpdating = False
Sheets("Report").Activate
ActiveSheet.Unprotect Password:="earth"

' Make sure chart legend is in correct position
Sheets("Report").Activate
ActiveSheet.ChartObjects("Chart 11").Activate
ActiveChart.ChartArea.Select
ActiveChart.Shapes("Group 23").Select
Selection.Width = 413
Selection.Height = 45
Selection.ShapeRange.Left = 65
Selection.ShapeRange.Top = 10

' Print completed report sheet
Sheets("Report").Activate
Range("A1:I56").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$56"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("E2:I4").Select

' Re-protect Worksheet
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="earth"

End Sub
-------------

Any ideas as to why it won't run properly? I'm using XL97.

Thanks,
Corey

[Dave Hawley]

Hi Corey


Set the TakeFocus******* Property of the CommandButton to False.

[Corman]

Thanks for the reply Dave.

Changed the focus for the button to false, but it still doesn't work.

Any other ideas?

Corey

[Tom Schreiner]

I'm no Excel expert but I have had problems similiar to yours from printing to missing formats. The best solution I have come across is to slow the macro down inbetween certain events. Especially repaints.

Copy this little sub into a standard module
Listed below

Slow down things a bit and that might help you.

This call to the sub would pause for about a second SD 1
You can use SD .1 to pause for one tenth of a second or just about any other measurement you need.


Sub SD(LenTime)
Dim Start
Start = Timer + LenTime
Do While Timer < Start
If Timer = 0 Then Start = _
Timer + 1
DoEvents
Loop
End Sub


[ This Message was edited by: TsTom on 2002-03-22 07:46 ]

[Dave Hawley]

It could be because you are Activating the Report sheet, but not selecting it.
Try:

ub PrintReport()

' Unprotect Worksheet


Sheets("Report").Unprotect Password:="earth"

Application.ScreenUpdating = False
' Make sure chart legend is in correct position
With Sheets("Report")
.Select
.ChartObjects("Chart 11").Activate
.ChartArea.Select
End With
ActiveChart.Shapes("Group 23").Select
Selection.Width = 413
Selection.Height = 45
Selection.ShapeRange.Left = 65
Selection.ShapeRange.Top = 10

' Print completed report sheet
Sheets("Report").Activate
Range("A1:I56").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$56"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Range("E2:I4").Select

' Re-protect Worksheet
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="earth"

End Sub

[Corman]

hmmm. Activating the sheet didn't help.


TsTom, how would I code to call the SD routine?

I tried to put this in the code above, with the SD routine in it's own module:

LenTime = 1
Call SD

But that didn't seem to work.

I even tried to put the resizing on it's own button, but no luck.

One thing that may help you guys: I originally had the code for resizing associated with an OK button for a userform, and it worked great. But I'd like the resizing to occur just before printing (I could try to have the PrintReport button connect to a userform that asks if I want to print, then have the resize code run if ok is pressed, but there has to be a better way to do it!).

Looking forward to your other ideas guys!

Corey

[Dave Hawley]

Corey, I think it maybe because you are using a CommanDButton from the Control toolbox. These become part of the sheet so are you calling this macro?
eg Run "PrintReport"

Go with a CommandButton from the Forms toolbar, much better for something like this.

[Tom Schreiner]

To call for a one second pause type:
SD 1 in any procedure in your project

The value is passed to the Sub without
pre-declaring LenTime.

SD .1 for a 1/10 sec pause

ect ect

[Corman]

Dave,

I created a new button from the forms toolbar, and attached the code to it, but when I run it I get an error for the following line of code:

"Object doesn't suuport this property or method"

error on this coding: ChartArea.Select

Corey