S
SEAN DI''''ANNO
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?
I don't know if this is this is part of problem but 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
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?
I don't know if this is this is part of problem but 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