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

Hi,

Could you provide more information?

For example, to what code are you referring here?

> 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

It seems there may be some conflict.

I don't see this how you are using this instruction:

> Selection.QueryTable.Refresh BackgroundQuery:=False ??

Dan

Hi Dan,

Thanks for taking the time to reply.
The code I found was this;


Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Customer Metrics
Menu").delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")

'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "&Customer Metrics Menu"

'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Update Metrics"
.FaceId = 50
.OnAction = "UpdateRoutine"
End With

What I did have and would like to have is;

With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Exit Metrics"
.FaceId = 51
.OnAction = "ExitRoutine"

Where the macro, ExitRoutine. would simply be;


Sub ExitRoutine ()
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&Customer Metrics
Menu").delete
On Error GoTo 0
Workbooks.Open Filename:="W:\Marketing\SAS Reports\Report Menu.xls"
End Sub

In regards to;
Selection.QueryTable.Refresh BackgroundQuery:=False ??

Each worksheet is linked to Access Data e.g.

Data/Get External Data....etc

Before I put in this line, when the Customer Metrics was opened from, The
Report Menu, it would not refresh and adding this line was the only way I
knew of acheiving it.



"dan dungan" wrote:

> Hi,
>
> Could you provide more information?
>
> For example, to what code are you referring here?
>
> > 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
>
> It seems there may be some conflict.
>
> I don't see this how you are using this instruction:
>
> > Selection.QueryTable.Refresh BackgroundQuery:=False ??
>
> Dan
>
>
>

Hi Sean,

>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?

When you get the application or user defined error, what line of code
is highlighted when you click on debug?

Dan