APPLICATION OR USER DEFINED ERROR

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a simple excel workbook called Report Menu. When it opens it has a
user form with labels which each hyper link to a different report. Eg;

Application.ThisWorkbook.FollowHyperlink "S:\Lists\Analysis\CVC
METRICS\Customer Metrics.xls"
Windows("Report Menu.xls").Activate
ActiveWorkbook.Close False

On each of the linked forms there is a command button currently to return to
the menu, eg;
Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls"

When the report menu opens, it runs on Open this;

Sub CheckIfOpen()

UserForm1.Show

On Error Resume Next
Workbooks("Customer Metrics.xls").Close SaveChanges:=False
On Error GoTo 0

End Sub


I have tested this and it works ok, ie. goes back and forth no problem
except that, I seem to get an egg timer which stays on for quite some time in
teh customer metrics. Is this becuase of this line;
Selection.QueryTable.Refresh BackgroundQuery:=False ??

My question is this, I found some code to have a user defined menu at the
top so I can for instance navigate to different sheets etc. When I use the
menu to do the same as the command button eg;
Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls"
It does return to the menu but, I get an APPLICATION OR USER-DEFINED ERROR?

Can anyone tell me why please;
 
I don't know if this will help The first time I open the Customer Metrics,

this code works;


Private Sub Workbook_Open()

Run ("AddMenus")
'-- in use to avoid use of volatile
Application.CalculateFull ' ctrl-alt-f9

Sheets("SAS Source").Visible = True
Sheets("SAS Source").Activate
Cells(5, 5).Select
Selection.QueryTable.Refresh BackgroundQuery:=False
On Error GoTo 0
Sheets("SAS Source").Visible = False
End Sub

....but when I return to the Report Menu and then reopen the customer
metrics, the code does not work, I.e. A new menu is not added
 
Back
Top