Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: macro runs fine in vb editor, but not on worksheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Corey


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



  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply Dave.

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

    Any other ideas?

    Corey

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •