Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

code runs fine the first time, then needs to be reset

  Asked By: Leon    Date: Nov 14    Category: MS Office    Views: 625
  

The following code runs fine once, then when
I step through a second time it doesn't select the sheet, chart, or
printout the chart until I do a Reset from the Run menu in the VBA
window. I'm sure its easy as I'm trying to learn this on the fly.
Any help is appreciated.

Bob

Private Sub PrintInboundAbbamonte_Click()
On Error GoTo Err_PrintInboundAbbamonte_Click

Dim App As Object

Set App = CreateObject("Excel.Application")
App.Visible = True
'Only XL 97 supports UserControl Property
On Error Resume Next
App.UserControl = True
App.Workbooks.Open ("C:\Documents and
Settings\RPhelps.NGC\Desktop\Inbound1.xls")
ActiveWindow.Visible = True
Windows("Inbound1.xls").Activate
Sheets("Steve Abbamonte").Select
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.PrintOut Copies:=1, Collate:=True
Windows("Inbound1.xls").Close
App.Quit

Exit_PrintInboundAbbamonte_Click:

Exit Sub

Err_PrintInboundAbbamonte_Click:
MsgBox Err.Description
Resume Exit_PrintInboundAbbamonte_Click

End Sub

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Oliver Jones     Answered On: Nov 14

So what _does_ it do? When you step through using F8, does anything
happen at all?

 
Answer #2    Answered By: Devin Ross     Answered On: Nov 14

It runs  fine until it comes time  to select  the sheet. It opens Excel
and the workbook and then closes it, without select the sheet,
chart, or printing it out.

 
Answer #3    Answered By: Aran Boonliang     Answered On: Nov 14

Taking out the line:
On Error Resume Next
will help  diagnose the problem!

 
Answer #4    Answered By: Vickie Smith     Answered On: Nov 14

After doing this I figured out the problem.............

 
Answer #5    Answered By: Lois Schmidt     Answered On: Nov 14

try this:

Private Sub PrintInboundAbbamonte_Click()
On Error GoTo Err_PrintInboundAbbamonte_Click

Dim App As Object

Set App = CreateObject("Excel.Application")
App.Workbooks.Open ("C:\Documents and
Settings\RPhelps.NGC\Desktop\Inbound1.xls")
App.Sheets("Steve Abbamonte").ChartObjects("Chart 6").Chart.PrintOut
Copies:=1, Collate:=True

App.Quit
Set App = Nothing 'this releases resources and maybe why yours needs
resetting?
Exit_PrintInboundAbbamonte_Click:

Exit Sub

Err_PrintInboundAbbamonte_Click:
MsgBox Err.Description
Resume Exit_PrintInboundAbbamonte_Click

End Sub

 
Didn't find what you were looking for? Find more on code runs fine the first time, then needs to be reset Or get search suggestion and latest updates.




Tagged:          

 
 
 
 

Related Post