macro runs fine in vb editor, but not on worksheet

Corman

New Member
Joined
Mar 20, 2002
Messages
27
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Thanks for the reply Dave.

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

Any other ideas?

Corey
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top